Variable to set criteria as string - is possible?

1

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 ??

Example
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
BEGIN
set @ExtraCriteria ='AND InputID = ' + Convert (varchar(5), @inputID)
END

PRINT @ExtraCriteria

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

2

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.

1 Like
3

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