一个具有不同值的字符串 [英] one String with Different Values

问题描述

大家好，

我使用以下命令在sql 2005中有一个Store Proc:-

从test1选择*
(@department)中的部门

我有一个包含a，b，c和d项的列表框.

我可以选择任何一项或多项.我想在@department中使用它.

我有问题.

1)@部门拿走的物品不超过一件.如果我们选择一项有效，而另一项则无效.

在此先感谢!!!

Hi All,

I have a Store Proc in sql 2005 with the below command:-

select * from test1
where department in (@department)

I have a listbox which have a,b,c and d items.

I user can select any one or more items. I would like to use that in @department.

I have a problems.

1) @department is not taking more then one item. If we select one item its working but more then one will not work.

Thanks in advance !!!

推荐答案

我认为存储过程参数不能采用多个值.相反，您可以做的是将项目列表作为逗号分隔的字符串传递，然后像这样构造查询:

I don''t think stored procedure parameters can take multiple values. What you could do instead is pass in the list of items as a comma delimited string, and then structure your query like so:

select * from test1
where
(@department = department) or
(@department like '%,' + department) or
(@department like department + ',%') or
(@department like '%,' + department + ',%')

或者，您可以编写一个SQL函数以将字符串解析为表变量，然后将表与表变量连接以查找匹配项.

Alternatively you could write a SQL function to parse the string into a table variable, then join your table with the table variable to find matches.

我认为此处解释的Execute 命令
http://msdn.microsoft.com/en-us/library/ms188332.aspx [ ^ ]
可通过构建如下所示的query string来实现此目的

I think the Execute command explained here
http://msdn.microsoft.com/en-us/library/ms188332.aspx[^]
can be used for this purpose by constructing the query string as shown below

EXECUTE ('select * from test1 where department in ' + @department)
--The @department is passed to the stored procedure as
-- ('a','b','c','d')

这篇关于一个具有不同值的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！