Building dynamic where condition in SQL statement

问题:

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'.

回答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

回答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