SQLTeam.com | Weblogs | Forums

Variable to set criteria as string - is possible?


If one variable in a stored procedure is empty or zero the IF BEGIN | END could be simplified for additional criteria. Seems a variable can be only used for an explicit value instead of something similar to this where the field name is embedded in the string.. Any way to do it ??

declare @cust table ( customer varchar(5), fod varchar(4), id int);
declare @inputID int = 2
declare @ExtraCriteria varchar(20)
insert into @cust(customer,fod,id)
values('FRED', 'ab', 1),('BOB','bc', 2),('SAM','bc',3);

Select * from @cust

if @inputID >0
set @ExtraCriteria ='AND InputID = ' + Convert (varchar(5), @inputID)

PRINT @ExtraCriteria

Select * from @cust where fod='bc' -- @ExtraCriteria -- would like to be able to dynamically add


It is possible. You will have to use dynamic SQL - see here and here.

You can write the query without using dynamic SQL - for example,
AND ( @InputId <= 0 OR InputId = @InputId)
However, those tend to generate poor execution plans in many cases.


thanks that will give some idea on the balance of simplicity readability and performance !