在SQL语句中构建动态where条件 [英] Building dynamic where condition in SQL statement

问题描述

我要基于存储过程输入来构建自定义Where条件，如果不为null，则将在语句中使用它们，否则将不使用它们。

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

但是出现此错误

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

推荐答案

使用此：

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

这篇关于在SQL语句中构建动态where条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！