如何在存储过程的子句中传递逗号分隔的值

Question

我得到这样的子句#ff0000，'#000'。但问题是我必须将那里的值作为参数传递给存储过程，以根据此颜色代码获取颜色名称。

这是我的存储过程

@ColorCode NVARCHAR(150)
AS
BEGIN
SET NOCOUNT ON;
SELECT * 
FROM color 
WHERE code IN (@ColorCode)

我想使用参数为IN子句传递这些值。我该怎么做？

请建议

Answer 1

试试这个

CREATE PROCEDURE Usp_split 
(
@ColorCode NVARCHAR(150)
) 
AS 
BEGIN 
-- SET NOCOUNT ON added to prevent extra result sets from 
-- interfering with SELECT statements. 
SET nocount ON; 
DECLARE @SPlitDAta TABLE 
( 
colorcode NVARCHAR(150) 
) 
INSERT INTO @SPlitDAta 
SELECT @ColorCode 
SELECT * 
FROM   color 
WHERE  code IN (SELECT split.a.value('.', 'VARCHAR(1000)') 
FROM   (SELECT Cast('<S>' + Replace(colorcode, ',', 
'</S><S>') 
+ '</S>' AS XML) AS ColorCode 
FROM   @SPlitDAta) AS A 
CROSS apply colorcode.nodes('/S') AS Split(a)) 
END