t-sql 将逗号分隔的字符串转换为 int,不使用用户创建的函数 [英] t-sql Convert comma separated string into int, without using user created function
问题描述
我正在传递一个整数列表(逗号分隔)
I'm passing a list of int's (comma separated)
即.1、2、3、4
给我的sp.但是我收到一个错误,因为列表是一个字符串,我正在与一个 int 字段进行比较.有没有办法在不使用用户创建的函数的情况下将列表转换为 int?
to my sp. But I'm getting an error because the list is a string, and I'm comparing to an int field. Is there a way for me to convert the list to int, without using a user created function?
注意:employeeID 是 INT
Note: employeeID is INT
declare @intArray varchar(200)
SELECT *
FROM tbl_Employee
WHERE employeeID IN ( @intArray )
错误是无法将 varchar 类型转换为 int"
The error is "Cannot convert type varchar to int"
推荐答案
您不想将该列表转换为 int,而是转换为 int 列表.
You don't want to cast that list into an int, but into a list of ints.
没有用于此的强制转换运算符或函数,但您可以使用动态 SQL 来解决它.
There is no cast operator or function for that, but you can use dynamic SQL to get around it.
基本上你写
EXECUTE('SELECT * FROM tbl_Employee WHERE employeeID IN ('+@intArray+')')
不过要注意 SQL 注入攻击!
Be aware of SQL injection attacks though!
这篇关于t-sql 将逗号分隔的字符串转换为 int,不使用用户创建的函数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!