Building dynamic where condition in SQL statement

Question

I want to build custom Where condition based on stored procedure inputs, if not null then I will use them in the statement, else I will not use them.

if @Vendor_Name is not null
    begin 

    set @where += 'Upper(vendors.VENDOR_NAME) LIKE "%"+ UPPER(@Vendor_Name) +"%"'

    end
    else if @Entity is not null
    begin
    set @where += 'AND headers.ORG_ID = @Entity'
    end
select * from table_name where @where

But I get this error

An expression of non-boolean type specified in a context where a condition is expected, near 'set'.

Galengalena

Answer 1

Use this :

Declare @Where NVARCHAR(MAX) 

...... Create your Where

DECLARE @Command NVARCHAR(MAX) 
Set @Command = 'Select * From SEM.tblMeasureCatalog AS MC ' ;

If( @Where <> '' )
   Set @Comand = @Command + ' Where ' + @Where

Execute SP_ExecuteSQL  @Command

I tested this and it Worked

Soggy

Answer 2

You cannot simply put your variable in normal SQL as you have in this line:

select * from table_name where @where;

You need to use dynamic SQL. So you might have something like:

DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Table_Name WHERE 1 = 1 ';
DECLARE @Params NVARCHAR(MAX) = '';

IF @Vendor_Name IS NOT NULL
    BEGIN
        SET @SQL += ' AND UPPER(vendors.VENDOR_NAME) LIKE ''%'' + UPPER(@VendorNameParam) + ''%''';
    END
ELSE IF @Entity IS NOT NULL
    BEGIN
        SET @SQL += ' AND headers.ORG_ID = @EntityParam';
    END;

EXECUTE SP_EXECUTESQL @SQL, N'@VendorNameParam VARCHAR(50), @EntityParam INT', 
                    @VendorNameParam = @Vendor_Name, @EntityParam = @Entity;

I assume your actual problem is more complex and you have simplified it for this, but if all your predicates are added using IF .. ELSE IF.. ELSE IF, then you don't need dynamic SQL at all, you could just use:

IF @Vendor_Name IS NOT NULL
    BEGIN
        SELECT  * 
        FROM    Table_Name
        WHERE   UPPER(vendors.VENDOR_NAME) LIKE '%' + UPPER(@Vendor_Name) + '%';
    END
ELSE IF @Entity IS NOT NULL
    BEGIN
        SELECT  * 
        FROM    Table_Name
        WHERE   headers.ORG_ID = @Entity;
    END

Exobiology

Answer 3

old question but want to add that i just found using

where CASE WHEN @id = 0 THEN 1 ELSE id END = CASE WHEN @id = 0 THEN 1 ELSE @id END

this is from these steps:

where id = @id

check if @id is null (or invalid) or have valid value so replace @id using case:

where id = 
CASE WHEN @id is null
    THEN 0
    ELSE @id
END

this is will end up as

where id = 0 -- if @id is null, still affected your query
where id = @id -- if @id have valid value

because we want use this "where" clause only if have valid data then we change id too

where 
CASE WHEN @id is null
    THEN 0
    ELSE id
END 
= 
CASE WHEN @id is null
    THEN 0
    ELSE @id
END

then will end up as

where 0 = 0 -- if @id is null and it doesn't affect your query
where id = @id -- if @id have valid value

don't forget if id is varchar, use 0 as '0'

CASE WHEN @id is null
    THEN '0'
    ELSE id
END 

i use this in my project and working well, even i use more than 1 of this in one query. please let me know if this query costing time at larger data

Stratiform

Answer 4

No need to use ad-hoc query (execute SP_ExecuteSQL)

Check below logic, you can use N number of dynamic / un-sure parameters / conditions

    -- flages
declare @chk_vendor bit;
declare @chk_entity bit;

-- setting off
set @chk_entity = 0;
set @chk_vendor = 0;

if @Vendor_Name is not null
        begin 
            set @chk_vendor = 1;
        end
    else if @Entity is not null
        begin
            set @chk_entity = 1;
        end


SELECT * 
FROM table_name 
WHERE (UPPER(vendors.VENDOR_NAME) LIKE '%' + 
       UPPER(@Vendor_Name) + '%' OR @chk_vendor = 0)
  AND (headers.ORG_ID = @Entity OR @chk_entity = 0)

I am posting this answer as there many similar questions , remember flag will just enable or disable a condition

Danelledanete

The chk_vendor and chk_entity are mutually exclusive. So instead of an IF/ELSE shouldn't it be just IF condition for each of them? – Celluloid

You can also avoid the BIT variables altogether. (UPPER(vendors.VENDOR_NAME) LIKE '%' + UPPER(@Vendor_Name) + '%' OR (@Vendor_Name IS NULL)) AND (headers.ORG_ID = @Entity OR (@Entity IS NULL)) – Celluloid