SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to check multiple combinations of a variable for null


how to check multiple combinations of a variable for null

Author
Message
brd123
brd123
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 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!
Bruce W Cassidy
Bruce W Cassidy
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1267 Visits: 1033
Can you give us some idea of what you're actually trying to accomplish?
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5348 Visits: 6900
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? Everybody look what's going down. -- Stephen Stills
brd123
brd123
SSC Journeyman
SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)SSC Journeyman (76 reputation)

Group: General Forum Members
Points: 76 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
Bruce W Cassidy
Bruce W Cassidy
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1267 Visits: 1033
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



The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5348 Visits: 6900
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? Everybody look what's going down. -- Stephen Stills
arun.sas
arun.sas
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1649 Visits: 3493
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
The Dixie Flatline
The Dixie Flatline
SSCertifiable
SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)SSCertifiable (5.3K reputation)

Group: General Forum Members
Points: 5348 Visits: 6900
or use (gasp) COALESCE Wink

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? Everybody look what's going down. -- Stephen Stills
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search