0

I am getting clause like this #ff0000,'#000'. But the problem is this I have to pass there values to a stored procedure as parameter to get the color names based on this color codes.

Here is my stored procedure 

@ColorCode NVARCHAR(150)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * 
    FROM color 
    WHERE code IN (@ColorCode)

I want to pass these value for the IN clause, using a parameter. How can I do this?

Please suggest

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gaurav_0093
  • 1,040
  • 6
  • 28
  • 56
  • 3
    Everytime you even *think* about using comma-separated values, 100 kittens are killed ..... ***DO NOT DO THAT!*** There are **much better** ways to do this!! Check out [**table-valued parameters**](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters) in SQL Server 2008 and up! – marc_s Aug 21 '17 at 09:09

1 Answers1

0

Try this

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