动态SQL逗号分隔值查询 [英] Dynamic SQL Comma-Delimited Value Query
问题描述
[更新:使用SQL Server 2005]
[Update: Using SQL Server 2005]
您好,我想用逗号分隔的值(id)列表查询存储过程,以检索数据行。
Hi, what I want to do is query my stored procedure with a comma-delimited list of values (ids) to retrieve rows of data.
我收到的问题是转换错误:
The problem I am receiving is a conversion error:
Conversion failed when converting the varchar value ' +
@PassedInIDs + ' to data type int.
我的从句和错误中的语句是:
The statement in my where-clause and error is:
...
AND (database.ID IN (' + @PassedInIDs + '))
注意:database.ID是int类型。
Note: database.ID is of int type.
我在以下位置关注文章:
I was following the article at:
http://www.sql-server-helper.com/functions/comma-delimited-to-table.aspx
但由于以下原因未完成错误。
but did not complete because of the error.
在执行脚本中,我有:
...
@PassedInIDs= '1,5'
我在这里做错了吗?
谢谢您的帮助。
Am I doing something wrong here? Thank you for your help.
推荐答案
我将创建CLR表值函数:
I would create a CLR table-valued function:
http://msdn.microsoft。 com / en-us / library / ms131103.aspx
在其中,您将字符串分开解析并转换为一组行。然后,您可以加入该表的结果,或使用IN查看列表中是否包含ID。
In it, you would parse the string apart and perform a conversion to a set of rows. You can then join on the results of that table, or use IN to see if an id is in the list.
这篇关于动态SQL逗号分隔值查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!