传递整个 WHERE 子句的 SQL 存储过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3085756/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Passing SQL stored procedure entirety of WHERE clause
提问by alpheus
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?
这能做到吗？
回答by Chris Wuestefeld
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 correctway 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 )
回答by Abe Miessler
I believe this can be done using Dynamic SQL. See below:
我相信这可以使用动态 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 之前，您应该对它进行一些认真的研究。以下是我快速搜索后发现的一些链接：
回答by Madhivanan
回答by Vicky1729
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.
一些答案中列出的动态 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
回答by guest
http://sqlmag.com/t-sql/passing-multivalued-variables-stored-procedure
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'
