Using sets as inparameters to function/sproc in SQL Server 2008?

Question

Hello!

Is it possible to use a set, like (1, 2, 3, 4, 5) for example, as inparameter to a Sproc, function or view in SQL Server 2008?

What should I use of Sproc, function or View for this pice of SLQ?

WITH Scores AS(
    SELECT
        ItemId, SUM(Score) AS Score

    FROM [Presenta].[presenta].[LpivScores]

    WHERE
        // HERE I want (1, 2, 3, 4, 5) to be the inparameter, like @inParam
        ListPropertyItemId IN (1, 2, 3, 4, 5)

    GROUP BY
        ItemId
)

// I want this to be returned
SELECT
    i.Id,
    s.Score
FROM
    Scores s,
    Items i
WHERE
    s.ItemId = i.Id

Any help is greatly appreciated!

Answer 1

Please see:

• T-SQL stored procedure that accepts multiple Id values
• Passing array as Parameter to SQL 2005 stored procedure

In SQL Server 2008, you can use table valued parameters.

Answer 2

You probably want to take a look at Table Valued Parameters.

Answer 3

Here a couple of more techniques to look at apart table valued parameters.

You can go ahead with comma separated values but you would need to create a function to split those comma separated values. http://vyaskn.tripod.com/passing%5Farrays%5Fto%5Fstored%5Fprocedures.htm

Other way is to use XML Parameters. http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx