declaring T-Sql parameter for comma delimited list of integers

Question

I have a table that contains a list of performances. These performances are grouped by production number. What I am trying to do is create a stored procedure that will return the last performance for each production entered. I would like to be able to input the production ids as a list of ids. Below is my procedure so far. Difficulty is I'm not sure how best to declare the @prod_no parameter to be used in the IN statement.

CREATE PROCEDURE IP_MAX_PERF_DATE 
-- Add the parameters for the stored procedure here
@prod_no 
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
SELECT [prod_season_no], MAX([perf_dt]) As max_dt FROM [T_PERF] WHERE [prod_season_no] IN (@prod)
GROUP By [prod_season_no];
END
GO

Any ideas

possible duplicate of [Passing a varchar full of comma delimited values to a SQL Server IN function](http://stackoverflow.com/questions/878833/passing-a-varchar-full-of-comma-delimited-values-to-a-sql-server-in-function) - see also the two linked questions that are provided there. — Ken White, Sep 13 '13 at 02:06

score 3 · Accepted Answer · answered Sep 13 '13 at 02:05

Try the sp_executesql

CREATE PROCEDURE IP_MAX_PERF_DATE 
@prod_no  nvarchar(500)
AS
BEGIN
SET NOCOUNT ON;
declare @statement nvarchar(1000)
set @statement = N'SELECT [prod_season_no], MAX([perf_dt]) As max_dt FROM [T_PERF] WHERE [prod_season_no] IN (' + @prod_no + ') GROUP By [prod_season_no]'
EXEC sp_executesql 
         @stmt = @statement
END
GO

score 2 · Answer 2 · answered Sep 13 '13 at 02:06

generally there are three ways to pass in a list of Ids:

Option 1: use comma separated list and split it in the stored procedure. this requires you to have a split function, or use dynamic sql (not preferred most of the time due to performance problem - at least hard to see the execution plan and you lose the point of using stored procedure to optimize your query)

Option 2: use xml, and again, you need to query the xml to find out the Ids

Option 3: use table valued parameter, this requires you to have a user defined table type

a detailed comparison could be found here: http://www.adathedev.co.uk/2010/02/sql-server-2008-table-valued-parameters.html

score 0 · Answer 3 · answered Sep 13 '13 at 11:56

This is what I've always done for passing in comma sepearted Integer IDs.

ALTER FUNCTION [dbo].[SplitArray]
(
@List varchar(500)
)
RETURNS 
@ArrayValues table
(
ListID int
)
AS
BEGIN
DECLARE @ListID varchar(10), @Pos int

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

IF REPLACE(@List, ',', '') <> ''
BEGIN
    WHILE @Pos > 0
    BEGIN
        SET @ListID = LTRIM(RTRIM(LEFT(@List, @Pos - 1)))
        IF @ListID <> ''
        BEGIN
            INSERT INTO @ArrayValues (ListID) 
            VALUES (CAST(@ListID AS int)) --Use Appropriate conversion
        END
        SET @List = RIGHT(@List, LEN(@List) - @Pos)
        SET @Pos = CHARINDEX(',', @List, 1)

    END
END 
RETURN
    END

To use it, simply join it on your query like so: Select a.* From Apples a Inner Join dbo.SplitArray(@IDList) array on a.AppleID = array.ListID

declaring T-Sql parameter for comma delimited list of integers

3 Answers3