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.
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...
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
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..
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)
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.
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.
@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) 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.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
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.
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.
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".
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.
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...
*** Read the sticky in the DB forum about how to get your question answered quickly!! ***
Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".