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,

May 2nd, 2006, 12:50 PM

szlamany

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

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)

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

szlamany

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 3rd, 2006, 04:31 PM

HoraShadow

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

May 3rd, 2006, 04:36 PM

szlamany

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, 05:51 AM

kaffenils

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.

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, 07:49 AM

waely

Re: stored procedure multi-select question

Quote:

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.

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 4th, 2006, 08:03 AM

kaffenils

Re: stored procedure multi-select question

Quote:

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 4th, 2006, 09:21 AM

szlamany

Re: stored procedure multi-select question

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

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

Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP)

End

Else

Begin

Set @SV=Cast(@ListStr asint)

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

May 4th, 2006, 10:44 AM

waely

1 Attachment(s)

Re: stored procedure multi-select question

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

May 4th, 2006, 11:55 AM

szlamany

Re: stored procedure multi-select question

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.