multivalued parameter in store procedure

see this article:
https://www.experts-exchange.com/A_1536.html

can you give more details on this?

create another wrapper sp
get the param, split with "," and call original sp

create procedure orgSP(@param1 varchar(max)) as
begin
 ...
end;

create procedure MultiValSP(@param1 varchar(max)) as
begin
 ... here split @param1 with "," and for each item call orgSP(@splited_item)
end;

check here for how to split delimited string into array

http://stackoverflow.com/questions/697519/split-function-equivalent-in-tsql

there are some functions here to split it into a table
create a cursor, loop it, for each item call original sp

also you can use CharIndex function to find the nth delimiter

select
CharIndex(', ','String 1, String 2, String 3',0),
CharIndex(', ','String 1, String 2, String 3',10)

9 19

>For ex: if the input of a parameter is ('110-santaclar', '111-sunnyval')

That's two parameters.  Do you mean this:
('110-santaclar, 111-sunnyval')

Thanks all for the response, I am actually taking the creating function route. Please find attached the function I created and the SP. But when I tried to exec the function:
exec peims.Split '001-carter-Riverside', '002-arlington'
I am getting an error saying that "The request for procedure 'Split' failed because 'Split' is a table valued function object", does that mean I create a scalar valued function rather than a table valued function ? Pls help.
And am a implementing it correct in the sp? - select value from peims.Split(substring(@locname, 1, 3) , ',')??

Yes dgmg, I meant ('110-santaclar, 111-sunnyval'), sorry about that, in my recent post, pls correct
exec peims.Split '001-carter-Riverside', '002-arlington'
to
exec peims.Split ('001-carter-Riverside, 002-arlington')

you have to do do select instead of exec to execute function

select peims.Split ('001-carter-Riverside, 002-arlington')

select * from peims.Split ('001-carter-Riverside, 002-arlington')

Hi Anil,
For some reason it is not able to recognize the function with peims schema, I tried

select peims.Split ('001-carter-Riverside, 002-arlington')

but it errors out saying :
Cannot find either column "peims" or the user-defined function or aggregate "peims.Split", or the name is ambiguous.

try

select * from peims.Split ('001-carter-Riverside, 002-arlington')

i missed * in previous post

It says insuffficient number of parameters "An insufficient number of arguments were supplied for the procedure or function peims.Split.
"
Pls find file attached which has code for function
function.txt

select * from peims.Split ('001-carter-Riverside, 002-arlington', ',')

please remove the space before the  (

Thanks for that, But I am having a problem when I include this in the stored procedure, I am only getting result set for the first value, the second value somehow is not been taken.
Pls suggest wht the problem is
procedure.txt

I am using the following exec statement

exec pmsMembershipCode 2, '001-carter-Riverside,002-arlington'

how do you call this? [peims].[pmsMembershipCode]

you need to create a cursor, loop it and for each iteration call [peims].[pmsMembershipCode]

have a look at here how to create cursor, loop it fetch rows until it ends...

http://msdn.microsoft.com/en-us/library/ms180169.aspx

Hi Anil,
Thanks for the correction, but I am still not getting the 2nd result set.

I am sorry I am getting the results, thanks for the efforts!

thnks

Executing the function aside, I think you've strayed from Kurt's solution.  

Instead of the creating a table-valued split function, just borrow the code from a split function and incorporate it into your procedure.

Put the split logic inside a stored proc what accepts your "multi-valued" parameter. Each time you isolate a term, then call the original procedure, passing the single-valued term.

In this case, you don't need a split function.  In fact, you don't really need a second procedure (though, modularizing that way may be a good idea).  You could just build a split-function-like loop inside your original procedure.  

   

if thats the case, even this is better

where ds.MEMBERSHIPCD = @memcode and ',' + @locname like '%,' + ds.LOCID + '-%'