[RESOLVED] stored procedure multi-select question

[waely]

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,

[szlamany]

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...)

[waely]

thank you but can you please give me an example? I appreciate that.

waely

[szlamany]

Go into QUERY ANALYZER and copy this into a query window...

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...

[kaffenils]

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

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)

[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?

[kaffenils]

@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.

[waely]

thank you szlamany. I'll try it and let you know.

your help is appreciated.
Waely

[waely]

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

[szlamany]

You need to EXECUTE the SPROC - you will no longer be performing that QUERY.

Have you written a STORED PROCEDURE yet?

[waely]

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

[HoraShadow]

@szlamany

WOW, that kicks ass

I keep getting that I have to spread more reputation, so I owe you two reps so far.

HoraShadow

[szlamany]

Thanks!

I use tricks like this all the time - so it's second nature to me

[waely]

thanks szlmany. problem is sloved.

waely