multivalued parameter in store procedure

Hi experts,
I have to modify a store proc to have ability to take multi valued parameters.
For ex: if the input of a parameter is ('110-santaclar', '111-sunnyval')
I should be able to accept one value at a time and pass it to the store proc, pls help.
sqlcuriousAsked:
Who is Participating?
 
anillucky31Commented:
see attached code
USE [Fwisddw]
GO
/****** Object:  StoredProcedure [peims].[pmsMembershipCode]    Script Date: 05/12/2011 16:37:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author: SHYAMALA BHIMAVARAPU
-- Create date: 05/12/2011
-- Description:	To identify all students by membership code.
-- exec pmsMembershipCode 2, '001-carter-Riverside,002-arlington'
-- =============================================
ALTER PROCEDURE [peims].[pmsMembershipCode]
	-- Add the parameters for the stored procedure here
   @memcode varchar(2), 
   @locname varchar(100)
   
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

   select distinct ds.locid as LOCID,  di.name as locationName, (ds.LOCID + di.name) as fullLocation,  di.peimsid , ds.permnum, di.lastname, di.firstname, di.middlename,
  ds.grade, di.ethniccode, ds.membershipcd , di.gender
 from dbo.dwSchEntWdraw ds left outer join dbo.dwIndicators di on 
ds.permnum = di.permnum 
where ds.MEMBERSHIPCD = @memcode and  ds.LOCID in (select SUBSTRING(LTRIM(RTRIM(value)), 1, 3) from peims.Split(@locname, ','))
order by ds.LOCID
END

GO 


--
--select value from peims.Split ('001-carter-Riverside, 002-arlington', ',')
--select LTRIM(Rtrim('      002-a'))

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
0
 
anillucky31Commented:
can you give more details on this?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
HainKurtSr. System AnalystCommented:
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;
0
 
HainKurtSr. System AnalystCommented:
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
0
 
HainKurtSr. System AnalystCommented:
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
0
 
dqmqCommented:
>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')

0
 
sqlcuriousAuthor Commented:
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) , ',')??

0
 
sqlcuriousAuthor Commented:
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')
0
 
anillucky31Commented:
you have to do do select instead of exec to execute function

select peims.Split ('001-carter-Riverside, 002-arlington')
0
 
anillucky31Commented:
select * from peims.Split ('001-carter-Riverside, 002-arlington')
0
 
sqlcuriousAuthor Commented:
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.
0
 
anillucky31Commented:
try

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

i missed * in previous post
0
 
sqlcuriousAuthor Commented:
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
0
 
anillucky31Commented:
select * from peims.Split ('001-carter-Riverside, 002-arlington', ',')
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please remove the space before the  (
0
 
sqlcuriousAuthor Commented:
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
0
 
sqlcuriousAuthor Commented:
I am using the following exec statement

exec pmsMembershipCode 2, '001-carter-Riverside,002-arlington'
0
 
HainKurtSr. System AnalystCommented:
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
0
 
sqlcuriousAuthor Commented:
Hi Anil,
Thanks for the correction, but I am still not getting the 2nd result set.
0
 
sqlcuriousAuthor Commented:
I am sorry I am getting the results, thanks for the efforts!
0
 
sqlcuriousAuthor Commented:
thnks
0
 
dqmqCommented:
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.  

   
0
 
HainKurtSr. System AnalystCommented:
if thats the case, even this is better

where ds.MEMBERSHIPCD = @memcode and ',' + @locname like '%,' + ds.LOCID + '-%'
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.