SQL - Stored Procedure with Select Statement using IN (@Variable_CommaDelimitedListOfIDS)

Question

I am creating a stored procedure to which I want to pass as variable a comma delimited list of Ids. I want to use the Ids into a select statement, something like:

Create Procedure up_TEST
@Ids VARCHAR(MAX)
AS
SELECT * FROM ATable a
WHERE a.Id IN(@Ids)

Obviously I get the error that @Ids is a varchar and not an INT but how can I convert the comma delimited list?

Answer 1

Since you're using SQL Server 2008, have a look at table-valued parameters.

Answer 2

For those cases I use this table function, which you can adapt to your needs:

CREATE FUNCTION dbo.f_params_to_list (@par VARCHAR(500))
returns @result TABLE (value VARCHAR(30))
AS  
begin
     DECLARE @TempList table
          (
          value VARCHAR(30)
          )

     DECLARE @Value varchar(30), @Pos int

     SET @par = LTRIM(RTRIM(@par))+ ','
     SET @Pos = CHARINDEX(',', @par, 1)

     IF REPLACE(@par, ',', '') <> ''
     BEGIN
          WHILE @Pos > 0
          BEGIN
               SET @Value = LTRIM(RTRIM(LEFT(@par, @Pos - 1)))
               IF @Value <> ''
               BEGIN
                    INSERT INTO @TempList (value) VALUES (@Value) --Use Appropriate conversion
               END
               SET @par = RIGHT(@par, LEN(@par) - @Pos)
               SET @Pos = CHARINDEX(',', @par, 1)

          END
     END    
     INSERT @result
     SELECT value 
        FROM @TempList
     RETURN
END    

In your stored procedure you would use it like this:

Create Procedure up_TEST
@Ids VARCHAR(MAX)
AS
SELECT * FROM ATable a
WHERE a.Id IN(SELECT value FROM dbo.f_params_to_list(@Ids))