传递整个WHERE子句的SQL存储过程

I have a SQL stored procedure of the form

我有一个表单的SQL存储过程

SELECT [fields] FROM [table] WHERE @whereSql

I want to pass the procedure an argument (@whereSql) which specifies the entire WHERE clause, but the following error is returned:

我想传递一个参数(@whereSql),它指定整个WHERE子句,但返回以下错误:

An expression of non-boolean type specified in a context where a condition is expected

Can this be done?

可以这样做吗?

5 个解决方案

#1

The short answer is that you can't do it like this -- SQL Server looks at the contents of a variable as a VALUE. It doesn't dynamically build up the string to execute (which is why this is the correct way to avoid SQL injection attacks).

简短的回答是你不能这样做 - SQL Server将变量的内容视为VALUE。它不会动态构建要执行的字符串(这就是为什么这是避免SQL注入攻击的正确方法)。

You should make every effort to avoid a dynamic WHERE as you're trying to do, largely for this reason, but also for the sake of efficiency. Instead, try to build up the WHERE clause so that it short-circuits pieces with lots of ORs, depending on the situation.

您应该尽一切努力避免动态的WHERE,因为您正在尝试这样做,主要是出于这个原因,但也为了提高效率。相反,尝试构建WHERE子句,以便根据情况使具有大量OR的片段短路。

If there's no way around it, you can still build a string of your own assembled from the pieces of the command, and then EXEC it.

如果没有办法绕过它,你仍然可以从命令的各个部分构建一个自己组装的字符串,然后执行它。

So you could do this:

所以你可以这样做:

DECLARE @mywhere VARCHAR(500)
DECLARE @mystmt VARCHAR(1000)
SET @mywhere = ' WHERE MfgPartNumber LIKE ''a%'' '
SELECT @mystmt = 'SELECT TOP 100 * FROM Products.Product AS p ' + @mywhere + ';'
EXEC( @mystmt )

But I recommend instead that you do this:

但我建议您这样做:

SELECT TOP 100 * 
    FROM Products.Product AS p 
    WHERE 
        ( MfgPartNumber LIKE 'a%' AND ModeMfrPartNumStartsWith=1)
    OR  ( CategoryID = 123 AND ModeCategory=1 )

#2

I believe this can be done using Dynamic SQL. See below:

我相信这可以使用Dynamic SQL完成。见下文:

CREATE PROCEDURE [dbo].[myProc]
@whereSql nvarchar(256)

AS
    EXEC('SELECT [fields] FROM [table] WHERE ' + @whereSql)
GO

That said, you should do some serious research on dynamic SQL before you actually use it. Here are a few links that I came across after a quick search:

也就是说,在实际使用它之前,你应该对动态SQL做一些认真的研究。以下是我在快速搜索后遇到的一些链接:

#3

Make sure you read this fully

请务必完整阅读

www.sommarskog.se/dynamic_sql.html

#4

http://sqlmag.com/t-sql/passing-multivalued-variables-stored-procedure

try this it works!!

试试这个有效!!

CHARINDEX (',' + ColumnName + ',', ',' +
REPLACE(@Parameter, ' ', '') + ',') > 0

execute syntax set @Parameter= 'nc1,nc2'

执行语法集@ Parameter ='nc1,nc2'

#5

Dynamic SQL listed in some of the Answers is definitely a solution. However, if Dynamic SQL needs to be avoided, one of the solutions that I prefer is to make use of table variables (or temp tables) to store the parameter value that is used for comparison in WHERE clause.

在一些Answers中列出的动态SQL绝对是一个解决方案。但是,如果需要避免使用动态SQL,我更喜欢的解决方案之一是利用表变量(或临时表)来存储WHERE子句中用于比较的参数值。

Here is an example Stored Procedure implementation.

以下是存储过程实现的示例。

CREATE PROCEDURE [dbo].[myStoredProc]
@parameter1 varchar(50)
AS

declare  @myTempTableVar Table(param1 varchar(50))
insert into @myTempTableVar values(@parameter1)

select * from MyTable where MyColumn in (select param1 from @myTempTableVar)

GO

In case you want to pass in multiple values, then the comma separated values can be stored as rows in the table variable and used in the same way for comparison.

如果要传递多个值,则逗号分隔值可以作为行存储在表变量中,并以相同的方式用于比较。

CREATE PROCEDURE [dbo].[myStoredProc]
@parameter1 varchar(50)
AS

--Code Block to Convert Comma Seperated Parameter into Values of a Temporary Table Variable
declare  @myTempTableVar Table(param1 varchar(50))
declare @index int =0, @tempString varchar(10)

if charindex(',',@parameter1) > 0
begin
 set @index = charindex(',',@parameter1)
 while @index > 0
  begin
    set @tempString = SubString(@parameter1,1,@index-1)
    insert into @myTempTableVar values (@tempString)
    set @parameter1 = SubString(@parameter1,@index+1,len(@parameter1)-@index)
    set @index = charindex(',',@parameter1)
  end

  set @tempString = @parameter1
  insert into @myTempTableVar values (@tempString)
end
else
insert into @myTempTableVar values (@parameter1)

select * from MyTable where MyColumn in (select param1 from @myTempTableVar)

GO

#1