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

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

[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

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

[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

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

[szlamany]

@kaffenils - thanks for the info. Normally we have the user enter that "comma-delimited" string in the client-side - so passing that varchar(100) string makes sense in most of our cases - but I will keep this XML trick in mind

@waely - use [vbcode] and [/vbcode] tags (or just "code" tags) around you sql and vb code - it will format it nicer.

This should work for you (and note that I do not have the "CREATE PROCEDURE" line from your SPROC - so I cannot see how you are defining the parameters:

...Create Procedure SomeSproc @Param1 int, @Param2 int, @ListStr varchar(100)
-- Note that I am assuming you have some parameters already - I've added this third
-- parameter to pass in the "list string"
 
Set NoCount On
 
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
 
-- We have now built our "table variable" with a row for each "list item"
 
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
 
[b]Where Id in (Select SelectValue From @ListTbl)[/b]
-- Something like this might work
GO

You mention that you are having speed issues with this VIEW and are going the SPROC route in hopes of fixing it.

If all you are doing is burying the VIEW in the SPROC you might not see any gain. Sometimes the best gain can be achieved by breaking down the VIEW and it's JOINS into parts - building TEMP TABLES first - and then constructing from the final product.

One way to see where the VIEW was having problems is to go into QUERY ANALYZER - select from the VIEW - and then look at the execution plan tab and it will tell you where the big I/O bottleneck is.

[waely]

szlamany -- when I looked at the execution plan I found that 89% is Nested Loop. check out the attached image and let me know please if it looks normal.

thank you

[szlamany]

This is telling you where you are spending all your time.

Is the ON-clause for this based on INDEXED columns in the child-table?

Based on what you are showing here you should be able to "mock-up" a simple SELECT with a JOIN to just this table that also takes this long to run. The reason I suggest this is that you will then have a "true" benchmark query that you can attempt adjustments to and find a clear solution to your problem.

[waely]

thanks szlmany. problem is sloved.

waely