CODEWITHSUNDEEP
sql-servert-sqlsql-server-2005
Wiki
Wiki

Reputation: 102

How to pass multiple values in a single parameter for a stored procedures?

I want to pass multiple values in a single parameter. SQL Server 2005

Upvotes: 2

Views: 6913

Answers (4)

Manoj Pandey
Manoj Pandey

Reputation: 1397

There are multiple ways you can achieve this, by:

  1. Passing CSV list of strings as an argument to a (N)VARCHAR parameter, then parsing it inside your SP, check here.

  2. Create a XML string first of all, then pass it as an XML datatype param. You will need to parse the XML inside the SP, you may need APPLY operator for this, check here.

  3. Create a temp table outside the SP, insert the multiple values as multiple rows, no param needed here. Then inside the SP use the temp table, check here.

  4. If you are in 2008 and above try TVPs (Table Valued Parameters) and pass them as params, check here.

Upvotes: 0

SQLMason
SQLMason

Reputation: 3275

You can make a function:

ALTER  FUNCTION [dbo].[CSVStringsToTable_fn] ( @array VARCHAR(8000) )
RETURNS @Table TABLE ( value VARCHAR(100) )
AS 
    BEGIN
        DECLARE @separator_position INTEGER,
            @array_value VARCHAR(8000)  

        SET @array = @array + ','

        WHILE PATINDEX('%,%', @array) <> 0 
            BEGIN
                SELECT  @separator_position = PATINDEX('%,%', @array)
                SELECT  @array_value = LEFT(@array, @separator_position - 1)

                INSERT  @Table
                VALUES  ( @array_value )

                SELECT  @array = STUFF(@array, 1, @separator_position, '')
            END
        RETURN
    END

and select from it:

DECLARE @LocationList VARCHAR(1000)
SET @LocationList = '1,32'

SELECT  Locations 
FROM    table
WHERE   LocationID IN ( SELECT   CAST(value AS INT)
                           FROM     dbo.CSVStringsToTable_fn(@LocationList) )

OR

SELECT  Locations
FROM    table loc
        INNER JOIN dbo.CSVStringsToTable_fn(@LocationList) list
            ON CAST(list.value AS INT) = loc.LocationID

Which is extremely helpful when you attempt to send a multi-value list from SSRS to a PROC.

Edited: to show that you may need to CAST - However be careful to control what is sent in the CSV list

Upvotes: 1

Darth Continent
Darth Continent

Reputation: 2319

You can have your sproc take an xml typed input variable, then unpack the elements and grab them. For example:

DECLARE @XMLData xml

DECLARE 
    @Code varchar(10),
    @Description varchar(10)

SET @XMLData = 
'
    <SomeCollection>
      <SomeItem>
        <Code>ABCD1234</Code>
        <Description>Widget</Description>
      </SomeItem>
    </SomeCollection>
'

SELECT 
    @Code = SomeItems.SomeItem.value('Code[1]', 'varchar(10)'),
    @Description = SomeItems.SomeItem.value('Description[1]', 'varchar(100)')
FROM @XMLDATA.nodes('//SomeItem') SomeItems (SomeItem)

SELECT @Code AS Code, @Description AS Description

Result:

Code        Description
==========  ===========
ABCD1234    Widget

Upvotes: 1

Oybek
Oybek

Reputation: 7243

Just to suggest. You can't really do so in SQL Server 2005. At least there is no a straightforward way. You have to use CSV or XML or Base 64 or JSON. However I strongly discourage you to do so since all of them are error prone and generate really big problems.

If you are capable to switch to SQL Server 2008 you can use Table valued parameters (Reference1, Reference2).

If you cannot I'd suggest you to consider the necessity of doing it in stored procedure, i.e. do you really want (should/must) to perform the sql action using SP. If you are solving a problem just use Ad hoc query. If you want to do so in education purposes, you might try don't even try the above mentioned things.

Upvotes: 0

Related Questions