Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to check multiple combinations of a variable for null Expand / Collapse
Author
Message
Posted Monday, March 9, 2009 4:16 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 4, 2011 1:28 PM
Points: 30, Visits: 66
Hi all... REsident newbie here... I am trying to check four variables for NULL values. Is there anyway I can do this besides writing out the different combinations of IF statements? E.g.:

IF @var1 IS NULL
SET @searchvar = 1
ELSE IF (@var2 is null and @var1 is null and var3 is null and var4 is not null) SET @searchvar = 'what a frickin mess of spagetti code'
END

Thanks!
Post #672030
Posted Monday, March 9, 2009 4:30 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 958, Visits: 1,031
Can you give us some idea of what you're actually trying to accomplish?
Post #672040
Posted Monday, March 9, 2009 4:34 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:09 PM
Points: 4,000, Visits: 6,058
I'll echo Bruce. If you must check all four variables for nulls, there is no getting around just doing it. But maybe if you'll tell us the WHY behind it, someone might have some ideas about alternatives. The only clue we have right now is that you are appearing to be setting up some kind of a search. Can you give us the bigger picture, please?

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #672042
Posted Monday, March 9, 2009 5:18 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, January 4, 2011 1:28 PM
Points: 30, Visits: 66
Yes, giving the user a choice of whether to search on product name, sku, descripition or product id. Recently discoverd that I can concatenate and execute a string e.g. exec(SELECT ' + @productid + ',' + @productname + ',' .... . But now the hard part on how to build the where clause. I am passing the contents of a checkbox control so i need to check for a 1 or 0. Any help or anyother way i can do this without chagning the business logic? thanks
Post #672059
Posted Monday, March 9, 2009 5:26 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:12 PM
Points: 958, Visits: 1,031
You might end up with something resembling this:
if object_id('dbo.MyProcedure') is not null
drop procedure dbo.MyProcedure;
--
-- MyProcedure: show how to use multiple parameters
--
create procedure MyProcedure(
@pIn_ProductName nvarchar(100) = null,
@pIn_StockUnit nvarchar(100) = null,
@pIn_ProductCode nvarchar(20) = null
)
as begin

set nocount on;

select ...
from ...
where
(@pIn_ProductName is null or ProductName like @pIn_ProductName) and
(@pIn_StockUnit is null or StockUnit like @pIn_StockUnit) and
(@pIn_ProductCode is null or ProductCode like @pIn_ProductCode);

return (0);
end; -- procedure

Post #672060
Posted Tuesday, March 10, 2009 6:51 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:09 PM
Points: 4,000, Visits: 6,058
If your first three parameters are simply the on/off values and the fourth parameter is the actual search string then

where
(@pIn_Product = 1 and ProductName like @searchString) or
(@pIn_StockUnit = 1 and StockUnit like @searchString) or
(@pIn_ProductCode = 1 and ProductCode like @searchString);

For performance reasons, to be sure to use any indexes correctly, I might write the store proc to use separate queries.

if @pIn_Produc = 1
begin
select blah
from something
where productName like @searchstring
end

else if @pIn_StockUnit = 1
begin
select blah
from something
where StockUnit like @searchstring
end

else if @pIn_ProductCode = 1
begin
select blah
from something
where ProductCode like @searchstring
end


Are Bruce and I close?


You also have potential solutions in dynamic SQL, but any way you go, you have to at some point list and test all the parms by name.


__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #672363
Posted Tuesday, March 10, 2009 9:54 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231, Visits: 3,483
Hi,

try this code

declare @var1 char(10)
,@var2 char(10)
,@var3 char(10)
,@var4 char(10)
, @searchvar char(10)

select @var1 = null
,@var2 = null
,@var3 = null
,@var4 = '123'

select @searchvar = case when @var2 is null and @var1 is null and @var3 is null and @var4 is not null then 'RESULT' else '1' end

select @searchvar



ARUN SAS
Post #672973
Posted Tuesday, March 10, 2009 10:11 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Friday, July 25, 2014 6:09 PM
Points: 4,000, Visits: 6,058
or use (gasp) COALESCE ;)

select @searchvar = case when COALESCE(@var1,@var2,@var3) is null and @var4 is not null then 'RESULT' else '1' end

But we believe the OP is trying to use @var4 as a search string, and @var1,@var2,@var3 are used to tell the procedure which column to search on.



__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? -- Stephen Stills
Post #672975
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse