Pass n parameters to a StoredProcedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Pass n parameters to a StoredProcedure

Author

Topic

alanmac
Starting Member

26 Posts

Posted - 2009-12-22 : 08:43:53

In my webpage the user gets to select one or more items from a list. Rather than having to pass each individual item to a stored procedure, is it possible to pass an unknown number of parameters decided at runtime? I would much rather call one SP once, passing it ALL the selected items, than call the SP for each item selected.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-12-22 : 08:49:39

Everything from the same list, like "Milk,eggs,cheese" and you want something like
WHERE product in ('Milk','Eggs','cheese')? If so you can use a table-valued function (search this site for fnParseValues) that will take a string 'Milk,eggs,cheese' and turn it into a table that you can then join to.

e.g.,

Select g.* from Grocery g inner join dbo.TableFunction('Milk,eggs,cheese') t
on g.product = t.product

Jim

Everyday I learn something that somebody else already knew

alanmac
Starting Member

26 Posts

Posted - 2009-12-22 : 08:58:15

I was thinking more of an insert:

INSERT <table> (Column1, Column2) VALUES(table2ID, SelectedItem)

table2ID will always be the same, but SelectedItem may be 'cheese', 'eggs' and/or 'Milk' or any combination

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-22 : 09:47:38

Do you want to insert each item as a different row in table2 or do you need 'Egg,Cheese,Milk' in the same column (Column2 in ur sample)

alanmac
Starting Member

26 Posts

Posted - 2009-12-22 : 10:50:00

My table has three columns: ID (Identity), ShopID (always the same for each call to the SP), ProductID (can be one or more products). For each product I want to add one entry to the table.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-22 : 11:09:32

Like Jim said..u can make use of Parse Values function..(found here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=104485).

Within the SP ..you can use this...(the input to ur SP will be the shopid and the comma separated product list I assume..)

declare @shopid int
declare @productlist varchar(1000)
set @shopid = 1
set @productlist = 'Eggs,Cheese,Milk'

Declare @tbl as table(shopid int,ProdList varchar(1000))

Insert into @tbl
Select @shopid,@productlist

INSERT INTO <urtable>
SELECT t.shopid,b.Val FROM @tbl t
CROSS APPLY ParseValues(t.ProdList)b

EDIT: @shopid and @productlist comes as input to the SP..i have just declared it here for testing the funciton.

alanmac
Starting Member

26 Posts

Posted - 2009-12-22 : 11:17:20

Many thanks, I will play about with that in the morning

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-12-22 : 11:21:51

Np. You're welcome. Post back if you face any trouble.

alanmac
Starting Member

26 Posts

Posted - 2009-12-23 : 02:35:15

It didn't like:

Declare @tbl as table(shopid int,ProdList varchar(1000))

so I changed it to:

Declare @tbl table(shopid int,ProdList varchar(1000))

It then complained about:

INSERT INTO <urtable>
SELECT t.shopid,b.Val FROM @tbl t
CROSS APPLY ParseValues(t.ProdList)b

Apparently there is incorrect syntax next to 'APPLY'. I have never used CROSS or APPLY, so don't know how to remedy this. By the way, I have changed a few things to point to my real tables and parameters, I am not just using the code you posted above.

Just discovered that although I am using SQL 2005 on my local machine, the development server is using SQl 2000, therefore APPLY isn't recognised as a valid clause.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources