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!

Greg Beech · Accepted Answer · 2009-12-28 11:15:35Z

3

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

answered Dec 28, 2009 at 11:15

Greg Beech

137k45 gold badges210 silver badges249 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 12:30:42Z

2

Please see:

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

edited May 23, 2017 at 12:30

CommunityBot

11 silver badge

answered Dec 28, 2009 at 11:12

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Comments

A G · Accepted Answer · 2009-12-28 11:17:31Z

1

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

answered Dec 28, 2009 at 11:17

A G

22.9k12 gold badges94 silver badges113 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related