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

Question

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!

score 3 · Answer 1 · answered Dec 28 '09 at 11:15

3

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

answered Dec 28 '09 at 11:15

Greg Beech

128,213
43
201
246

score 2 · Accepted Answer · edited May 23 '17 at 12:30

2

Please see:

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

edited May 23 '17 at 12:30

Community

1
1

answered Dec 28 '09 at 11:12

Mitch Wheat

288,400
42
452
532

score 1 · Answer 3 · answered Dec 28 '09 at 11:17

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_arrays_to_stored_procedures.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

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

3 Answers3