Passing multiple value to a parameter

Question

Passing multiple value to a parameter

yudy.varma

Right there with Babe

Points: 755
More actions
September 11, 2012 at 6:29 am

#143882

--Exec L1 'Bitumen VG 30 (60/70)'
Alter Proc L1
@Itm Varchar(50) = NULL
As
Begin
Set NoCount ON
select E.Ven_Ven as ven,F.Itm_Itm as itm from ERP_Pur_Ord A
Join ERP_Pur_Ord_ID B On A.Pur_Ord_OU_Code = B.Pur_Ord_OU_Code and A.Pur_Ord_Code = B.Pur_Ord_Code
Join ERP_Ven E On A.Pur_Ord_OU_Code = E.Ven_OU_Code and A.Pur_Ord_Txn_Loc = E.Ven_Txn_Loc AND A.Pur_Ord_Ven = E.Ven_Code
Join ERP_Itm F On A.Pur_Ord_OU_Code = F.Itm_OU_Code and B.ID_Itm = F.Itm_Code
where Itm_Itm IN (select Itm_Itm from dbo.split( @Itm ) )
END
can anybody tell me how to pass multiple values to a parameter

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

t.brown 89142 SSCommitted Points: 1530 More actions · Answer 1

it looks like dbo.split( @Itm ) is a function that splits a string into table

try looking at the function, work out what input format it is expecting (it probably splits @Itm on a delimiter hard-coded into the function)

to view the function code

EXEC sp_helptext split

so you might find the delimiter is ';' - in which case SET @Itm = 'Val1;Val2;Val3' and pass that to your Proc L1.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

I would start by looking closely at your split function. I am going to take a guess that is either a loop or xml based. Take a look at the link in my signature about splitting strings for a far more efficient way of doing that task.

Typically if you want to pass multiple parameters you delimit your single parameter (comma is the most common). So you would call your proc like this.

exec L1 @Itm = '1,2,3,4,5,6'

In this way I passed a single parameter that will be parsed in the calling procedure.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/