Problem in handling null values in stored proc

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)

Problem in handling null values in stored proc

Author

Topic

gamaz
Posting Yak Master

104 Posts

Posted - 2010-05-13 : 12:49:34

Hi,
The following is part of a stored procedure which works fine when parameters are passed to @StartingSlsman and @EndingSlsman
However the new business rule requires that these two parameters may not have any value (i.e. these may be optional).
In that case the null values of the the two parameters is producing no result in the output.
Current code:
SELECT TOP 1 @slsman = tegCustXRef_Temp3.slsman
,@cust_name = custaddr.name
,@uf_collectr = customer.uf_collectr --Added AS 05/11/10
,@customer_rowpointer = customer.rowpointer
FROM tegCustXRef_Temp3 INNER JOIN
customer ON tegCustXRef_Temp3.cust_num = customer.cust_num AND
tegCustXRef_Temp3.Cust_Seq = customer.cust_seq INNER JOIN
custaddr ON customer.cust_num = custaddr.cust_num AND customer.cust_seq = custaddr.cust_seq
where customer.cust_num = @cust_num and

and isnull(tegCustXRef_Temp3.slsman,'') between @StartingSlsman and @EndingSlsman

So I am trying to change the last line of code to handle the null parameter values in the parameters passed @startingslsman and @endingslsman.

I am changing the code as follows

If (@Startingslsman = null and @endingslsman = null) then
-- Do Nothing
else
tegCustXRef_Temp3.slsman between @StartingSlsman and @EndingSlsman
End

However with this change I am having errors when running the stored proc.

I appreciate any help on this. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 12:53:34

make it like


...
where customer.cust_num = @cust_num and

and (isnull(tegCustXRef_Temp3.slsman,'') >= @StartingSlsman or @StartingSlsman is null)
and (isnull(tegCustXRef_Temp3.slsman,'') <= @EndingSlsman  or @EndingSlsman  is null)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gamaz
Posting Yak Master

104 Posts

Posted - 2010-05-13 : 13:02:10

Thanks Visakh for your help again. It worked perfect. Let me ask you this. In the statement you wrote I am not understanding how the is null part is working.

Also does if statement work in a where clause the way I wrote it. I would like your comment on this if you have time. Thanks again.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 13:08:53

if statement cannot be used within select.the or @param is null will bypass the filter condition when param is null and so in effect its like filter is not present.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

gamaz
Posting Yak Master

104 Posts

Posted - 2010-05-13 : 13:16:23

Sounds good. Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-13 : 13:32:51

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources