T-SQL 将 CSV 字符串传递到存储过程时出现问题
我有那个过程,它返回由 ID 与传递的参数关联的行,即 1,5,7,9
I have that procedure which returns rows associated by ID with passed argument, i.e 1,5,7,9
ALTER PROCEDURE [dbo].[get_data]
@MyCodes as varchar(max) = ''
AS
BEGIN
DECLARE @query as nvarchar(max)
set @query = 'SELECT name FROM user WHERE id IN (@p_MyCodes)'
exec SP_EXECUTESQL @query,
N'@p_MyCodes varchar(max)',
@p_MyCodes = @MyCodes
END
该过程产生一个错误:将数据类型 varchar 转换为数字时出错.当我作为参数传递时,例如3,7,5
That procedure generates an error : Error converting data type varchar to numeric. when I pass as an argument e.g. 3,7,5
怎么了?
我认为这不会实现您的期望.您遇到的错误是因为它无法将字符串 '3,7,5' 转换为数字(请注意,它不会尝试解析您的各个值).
I don't think this is going to accomplish what you are expecting it to. The error you are getting is because it can't convert the string '3,7,5' to a number (note that it is NOT trying to parse out your individual values).
获得您想要的东西的两种方式:
Two ways to get what you want:
1) 创建一个接受 CSV 字符串并返回结果的表值函数(我确定网上有很多;这是一个相关问题:T-SQL 中的等价拆分函数?).这很好,因为您可以完全摆脱 SP_EXECUTESQL sproc 调用;您的查询变为:
1) Create a table value function that takes a CSV string and returns the results (I'm sure there are many on the web; Here's a related question: Split function equivalent in T-SQL?). This is nice because you can get rid of the SP_EXECUTESQL sproc call entirely; Your query becomes:
SELECT name FROM user where id IN (SELECT value FROM dbo.f_Split(@p_MyCodes))
2) 将您的设置更改为:
2) Change your set to something like:
set @query = 'SELECT name FROM user WHERE id in (' + @p_MyCodes + ')'
我不推荐 #2,它提供了一个 SQL 注入漏洞.
I don't recommend #2, it offers a SQL injection hole.