?
Solved

multivalued parameter in store procedure

Posted on 2011-05-13
24
Medium Priority
?
372 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:sqlcurious
  • 9
  • 6
  • 5
  • +2
24 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35754473
0
 
LVL 9

Expert Comment

by:anillucky31
ID: 35754493
can you give more details on this?
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35754635
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 61

Expert Comment

by:HainKurt
ID: 35754656
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 35754681
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
 
LVL 42

Expert Comment

by:dqmq
ID: 35755107
>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
 

Author Comment

by:sqlcurious
ID: 35755113
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
 

Author Comment

by:sqlcurious
ID: 35755132
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
 
LVL 9

Expert Comment

by:anillucky31
ID: 35755164
you have to do do select instead of exec to execute function

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

Expert Comment

by:anillucky31
ID: 35755189
select * from peims.Split ('001-carter-Riverside, 002-arlington')
0
 

Author Comment

by:sqlcurious
ID: 35755220
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
 
LVL 9

Expert Comment

by:anillucky31
ID: 35755235
try

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

i missed * in previous post
0
 

Author Comment

by:sqlcurious
ID: 35755311
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
 
LVL 9

Expert Comment

by:anillucky31
ID: 35755329
select * from peims.Split ('001-carter-Riverside, 002-arlington', ',')
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35755409
please remove the space before the  (
0
 

Author Comment

by:sqlcurious
ID: 35755524
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
 

Author Comment

by:sqlcurious
ID: 35755536
I am using the following exec statement

exec pmsMembershipCode 2, '001-carter-Riverside,002-arlington'
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35755561
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
 
LVL 9

Accepted Solution

by:
anillucky31 earned 2000 total points
ID: 35755566
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
 

Author Comment

by:sqlcurious
ID: 35755617
Hi Anil,
Thanks for the correction, but I am still not getting the 2nd result set.
0
 

Author Comment

by:sqlcurious
ID: 35755640
I am sorry I am getting the results, thanks for the efforts!
0
 

Author Closing Comment

by:sqlcurious
ID: 35755646
thnks
0
 
LVL 42

Expert Comment

by:dqmq
ID: 35755667
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 35755702
if thats the case, even this is better

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question