CODEWITHSUNDEEP
sql-server-2008stored-procedures
anilpab
anilpab

Reputation: 37

How to pass list of items as parameter to a stored procedure

I have a stored procedure

create PROCEDURE [dbo].[SP] 
(
    @OrderList varchar(500)
)
AS
Begin
    select * 
    from table 
    where id in ('+ @OrderList +')

Here I am passing orderlist....

When I execute like this

exec sp 'iss005,iss006'

I am not getting data

but when I hardcode in sp like this ...

   select * from table where id in ('iss005','iss006')

then am getting data...

Thank you

Upvotes: 3

Views: 23280

Answers (3)

Sapnandu
Sapnandu

Reputation: 642

I had the same kind of requirement. i was getting list of user in a int list variable and i need to get all the order of those user. I have use a very simple trick which had solve my issue. please find the code.

public DataTable GetAllOrderData(List<int> UserID)
    {
        try
        {
            string listofuser = String.Join(",", UserID.ToArray());
            SqlParameter[] parameters = new SqlParameter[]
            {                
                new SqlParameter("@USERID", listofuser)
            };
            return SqlDBHelper.ExecuteParamerizedSelectCommand("GetOrderByUserID", System.Data.CommandType.StoredProcedure, parameters);
        }
        finally { UserID = null; }

    }

And this is the stored procedure

CREATE PROCEDURE [dbo].[GetOrderByUserID] (@USERID varchar(700))
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
Declare @SQL VarChar(1000)
Select @SQL = 'SELECT *,ORM.OrganisationName FROM OrderTransaction ORT LEFT JOIN OrganisationMaster ORM ON (ORT.OrganisationID=ORM.OrganisationID) '
Select @SQL = @SQL + 'WHERE ORT.CreatedBy IN (' + @USERID +')'

Exec ( @SQL)
END

Upvotes: 0

Pedro Angel
Pedro Angel

Reputation: 116

I strongly recommend in this case the use of XML parameters, will give you a lot of flexibility.

Your XML might be something like

<ids>
   <id>iss006</id>
   <id>iss005</id>
</ids>

Your procedure should be something like this:

create PROCEDURE [dbo].[SP] 
(
    @OrderList XML
)
AS
Begin
    select *  from table 
    where id in (
            select ParamValues.ID.value('.','VARCHAR(50)') 
                    FROM @OrderList.nodes('/ids/id') as ParamValues(id)
            )

Besides the use of store procedures outputs I also would recommend the use of functions but that is up to you. Regards.

Upvotes: 3

Neil Knight
Neil Knight

Reputation: 48537

Unfortunately it won't work that way. If you change your procedure to something like the following, this will work:

Create Procedure dbo.SP
    @OrderList varchar(500)
AS

Declare @SQL VarChar(1000)

Select @SQL = 'SELECT * FROM table '
Select @SQL = @SQL + 'WHERE id in (' + @OrderList +')'

Exec ( @SQL)

GO

Looking more into your query, your ID's value varchar, so the procedure will fail as you'll still be getting :

WHERE id in (iss005,iss006)

when you want :

WHERE id in ('iss005','iss006')

You would need to either pass in the quote values, e.g. :

@OrderList = 'iss005','iss006'

Or work out some SQL to split the @OrderList by comma and use the QUOTENAME() function to add the quotes to the new variable.

Upvotes: 5

Related Questions