March 9, 2009 at 4:16 pm
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!
March 9, 2009 at 4:30 pm
[font="Verdana"]Can you give us some idea of what you're actually trying to accomplish?[/font]
March 9, 2009 at 4:34 pm
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
March 9, 2009 at 5:18 pm
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
March 9, 2009 at 5:26 pm
[font="Verdana"]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
[/font]
March 10, 2009 at 6:51 am
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
March 10, 2009 at 9:54 pm
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
March 10, 2009 at 10:11 pm
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? Everybody look what's going down. -- Stephen Stills
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply