我必须将整数列表作为字符串从前端传递到SQL Server数据库,例如:(2,3,4,5)
ALTER PROCEDURE GetMultipleCustomerByID
@Id nvarchar(50)
AS
BEGIN
SELECT
Firstname, Lastname
FROM
Customer
WHERE
CustomerID IN (@Id)
END
Exec GetMultipleCustomerByID "2,3,4,5"
上面表示的值(2,3,4,5)是表的ID值(主键)。
从这些id中,我必须从表中检索行。字符串"2,3,4,5"可以是动态的。可包含多于或少于4个数字
有多种方法可以解决这个问题,但一个简单的方法是动态SQL:
ALTER PROCEDURE GetMultipleCustomerByID (
@Ids nvarchar(50)
) as
BEGIN
declare @sql nvarchar(max);
set @sql = 'select Firstname, Lastname from Customer where CustomerID in (@Ids)';
set @sql = replace(@sql, '@Ids', @Ids);
exec sp_executesql @sql;
END;
Exec GetMultipleCustomerByID "2,3,4,5";