-
May 2nd, 2006, 12:12 PM
#1
Thread Starter
Addicted Member
[RESOLVED] stored procedure multi-select question
hi,
is it possible to design a stored procedure to expect unknown number of parameters.
for example; I have a select statmenet that retrieved number of records based on what the user selections. so user might select 1 record or 10 records to show. I want to convert the select statement to Stored Procedure but I can't specify the parameters in the stored procedure becasue I wouldn't know how many the user might select.
what is the best solution arround it??
in my select statement I build my Where claus based on the user selections so it would be " id=1 OR id=20 Or 23 Or......" and don't know who to do something similar in stored procedure if it's possible at all....
thanks everyone,
Last edited by Hack; May 5th, 2006 at 06:05 AM.
Reason: Added green "resolved" checkmark Last edited by waely : Yesterday at 03:12 PM.
-
May 2nd, 2006, 12:50 PM
#2
Re: stored procedure multi-select question
You can allow the user to enter 10,20,30,35,36,37 - pass it to the SPROC as a varchar(100) parameter for instance.
Then in the SPROC you cut up the parameter - put each value into a TABLE VARIABLE.
Then you can join to that table variable or do a WHERE SOMECOL IN (SELECT...)
Last edited by szlamany; Feb 21st, 2007 at 01:49 PM.
-
May 2nd, 2006, 01:46 PM
#3
Thread Starter
Addicted Member
Re: stored procedure multi-select question
thank you but can you please give me an example? I appreciate that.
waely
-
May 2nd, 2006, 06:31 PM
#4
Re: stored procedure multi-select question
Go into QUERY ANALYZER and copy this into a query window...
Code:
Set NoCount On
Declare @ListStr varchar(100)
Set @ListStr='10,20,30,35,36,37'
Declare @ListTbl Table (SelectValue int)
Declare @CP int
Declare @SV int
While @ListStr<>''
Begin
Set @CP=CharIndex(',',@ListStr)
If @CP<>0
Begin
Set @SV=Cast(Left(@ListStr,@CP-1) as int)
Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP)
End
Else
Begin
Set @SV=Cast(@ListStr as int)
Set @ListStr=''
End
Insert into @ListTbl Values (@SV)
End
Select * From @ListTbl
So what you see with this example is that you can take a STRING of comma-separated values and put them into a TABLE VARIABLE (or a TEMPORARY TABLE if desire).
Then you do your query like this:
Select * From SomeTable Where Id in (Select SelectValue From @ListTbl)
Or...
Select * From @ListTbl LT
Left Join SomeTable ST on ST.Id=LT.SelectValue
or I'm sure several other variations would work as well...
-
May 4th, 2006, 05:51 AM
#5
Re: stored procedure multi-select question
Another way of sending multi value parameters is by using xml. I have used it a lot the last months and it works perfectly. You don't even have to be conserned about parameter values containing the list separator.
Example related to your question
Code:
declare hxmldoc int, @xml varchar(8000)
set @xml='<parameters>
<parameter value="10"></parameter>
<parameter value="20"></parameter>
<parameter value="30"></parameter>
<parameter value="35"></parameter>
<parameter value="36"></parameter>
<parameter value="37"></parameter>
</parameters>'
declare @params table(value int)
exec sp_xml_preparedocument @hxmldoc OUTPUT, @xml
insert into @params
select * from openxml(@hxmldoc, '/parameters/parameter',1)
with (value int)
exec sp_xml_removedocument @hxmldoc
Select * From SomeTable Where Id in (Select valueFrom @params)
-
May 4th, 2006, 06:44 AM
#6
Re: stored procedure multi-select question
@kaffenils - very interesting...
From what I see it's exactly the same logic but using the XML parser to break down the parameter list.
Is the sp_xml_preparedocument, openxml or sp_xml_removedocument functions expensive? How and where do they materialize the document for use?
-
May 4th, 2006, 08:03 AM
#7
Re: stored procedure multi-select question
Originally Posted by szlamany
@kaffenils - very interesting...
From what I see it's exactly the same logic but using the XML parser to break down the parameter list.
Is the sp_xml_preparedocument, openxml or sp_xml_removedocument functions expensive? How and where do they materialize the document for use?
I have only worked with small xml documents, so I really can't say how it will perform with large ones. The sp_xml_preparedocument uses the xml parser in msxml2.dll and formats and copies it to memory so that OPENXML can read it.
-
May 3rd, 2006, 07:04 AM
#8
Thread Starter
Addicted Member
Re: stored procedure multi-select question
thank you szlamany. I'll try it and let you know.
your help is appreciated.
Waely
-
May 3rd, 2006, 02:57 PM
#9
Thread Starter
Addicted Member
Re: stored procedure multi-select question
hi,
your code works great and the result is table with my ID's.
now I have two questions:
1- replacing my sqlstr with stored procedure:this is my old code:
strSQL = "SELECT * FROM TABLE NAME WHERE condition..
oCmd = New System.Data.SqlClient.SqlCommand(strSQL)
I want to replace my strSQL to call stored procedure "[StudyList]" and pass the the paramter "@ListStr"
2- not sure how to incorporate this "temp table" to my existing SPROC..
sorry for all these questions :-)
and thank you
-
May 3rd, 2006, 03:10 PM
#10
Re: stored procedure multi-select question
You need to EXECUTE the SPROC - you will no longer be performing that QUERY.
Have you written a STORED PROCEDURE yet?
-
May 4th, 2006, 07:49 AM
#11
Thread Starter
Addicted Member
Re: stored procedure multi-select question
Originally Posted by szlamany
You need to EXECUTE the SPROC - you will no longer be performing that QUERY.
Have you written a STORED PROCEDURE yet?
szlamany,
yes, I have the stored procedure. I actually converted my Table View to stored procedure because it was running very slow and I hoped that with converting it to stored procedure I will get a better performance.
here is part of my stored procedure:
-------------------------------------------
SELECT TOP 100 PERCENT
code AS TA, code2 AS CT, ProjName AS Project, Phase, Protocol,
fields, ProjectName AS [Study Name],
CONVERT(char(10), POD_Op, 101) AS [POD],POD_Note AS [Comment], CONVERT(char(10), Op, 101) AS [IND],Note AS [NComment], CONVERT(char(10), POp, 101) AS [Approved],
dbo.vwMSP_KPI_Singl_Sorted.PrtclA_Note AS [Protocol
FROM Fields LEFT OUTER JOIN PKSuppl2 ON
PROJ_ID = ProjectUniqueID LEFT OUTER JOIN
OutlineCodes ON Fields.PROJ_ID = PROJ_ID LEFT OUTER JOIN
Sorted ON dbo.vwMSP_Project_Fields.PROJ_ID = ProjectUniqueID
GO
----------------------------------------------
this is just part of my real SProc. I changed the names arround for security so if something didn't make sence it's becasue I changed that names arround. but my version works good.
my select statement is way too long and I only copied 10% and would like to know how to incorporate the codes you gave me into the WHERE statement.
thank you very much,
Waely
-
May 3rd, 2006, 04:31 PM
#12
Re: stored procedure multi-select question
@szlamany
WOW, that kicks ass
I keep getting that I have to spread more reputation, so I owe you two reps so far.
HoraShadow
I do like the reward system. If you find that my post was useful, rate it.
-
May 3rd, 2006, 04:36 PM
#13
Re: stored procedure multi-select question
Thanks!
I use tricks like this all the time - so it's second nature to me
-
May 4th, 2006, 02:11 PM
#14
Thread Starter
Addicted Member
Re: stored procedure multi-select question
thanks szlmany. problem is sloved.
waely
-
Aug 21st, 2017, 09:16 AM
#15
Re: [RESOLVED] stored procedure multi-select question
Steve - is this still the best way to do this? For SQL Server 2005? (Not sure what the OP had, but my company hasn't upgraded much over the years). Thanks.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Aug 21st, 2017, 09:43 AM
#16
Re: [RESOLVED] stored procedure multi-select question
Originally Posted by MMock
Steve - is this still the best way to do this? For SQL Server 2005? (Not sure what the OP had, but my company hasn't upgraded much over the years). Thanks.
Yes - this is still the way that I do this type of task.
I might have seen other ideas lately - I'll look around...
-
Aug 21st, 2017, 09:47 AM
#17
Re: [RESOLVED] stored procedure multi-select question
This is great! Simple to implement, works perfectly! I'm good with it. Thanks for all your great contributions!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|