Need urgent help in figuring a stored procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need urgent help in figuring a stored procedure

Author

Topic

gamaz2
Starting Member

31 Posts

Posted - 2008-12-11 : 21:38:47

Hi,
I have a stored procedure that has few temporary tables for manipulating
records from multiple tables. In the final temp table i have the following
code:
create table #temp4
(
prr_id int,
project_num varchar(11)
,project_desc ntext
,engineer varchar(60)
,plant varchar(20)
,part_num varchar(20)
,prr_in_added_date datetime
,poduedate datetime
)
insert into #temp4
select
tblprrin.prr_id,
#temp3.project_num
,#temp3.project_desc
,#temp3.engineer
,#temp3.plant
,case when overridepart = 0
then tblprrin.partnumberprs
else tblprrin.partnumberprr
end as partnumber
,tblprrin.prr_in_added_date
,tblprrin.poduedate

from #temp3 inner join
tblprrin on tblprrin.projectno = #temp3.project_num
and tblprrin.plantprs = #temp3.plant

However, this works for a scenario where plant value is not null. However
few of the value of plant corresponding to a projectno is null. In such a
scenario the above code does not work. However if the last line is
conditionally dropped for null plant then this code works for even scenarios
where plant is null. Thus the condition I am trying to build is that if
#temp3.plant is null then the code will not include the last line i.e. and
tblprrin.plantprs = #temp3.plant while in other scenario the last line should
be included.
Is there any way to accomplish this? Any help is appreciated. Thanks.

Welcom Jam
Starting Member

3 Posts

Posted - 2008-12-12 : 07:32:29

Try:

and tblprrin.plantprs = ISNULL(#temp3.plant, tblprrin.plantprs)