Results 1 to 20 of 20

Thread: [RESOLVED] stored procedure multi-select question

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Nov 2002
    Posts
    150

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

  2. #2
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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.

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

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Nov 2002
    Posts
    150

    Re: stored procedure multi-select question

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

    waely

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

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

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

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Nov 2002
    Posts
    150

    Re: stored procedure multi-select question

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

    your help is appreciated.
    Waely

  6. #6

    Thread Starter
    Addicted Member
    Join Date
    Nov 2002
    Posts
    150

    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

  7. #7
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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?

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

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  8. #8
    Fanatic Member
    Join Date
    May 2005
    Posts
    608

    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.

  9. #9
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: stored procedure multi-select question

    Thanks!

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

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

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  10. #10
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    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)

  11. #11
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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?

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

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  12. #12

    Thread Starter
    Addicted Member
    Join Date
    Nov 2002
    Posts
    150

    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.

    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

  13. #13
    Fanatic Member kaffenils's Avatar
    Join Date
    Apr 2004
    Location
    Norway
    Posts
    946

    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.

  14. #14
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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:

    VB Code:
    1. ...Create Procedure SomeSproc @Param1 int, @Param2 int, @ListStr varchar(100)
    2. -- Note that I am assuming you have some parameters already - I've added this third
    3. -- parameter to pass in the "list string"
    4.  
    5. Set NoCount On
    6.  
    7. Declare @ListTbl Table (SelectValue int)
    8. Declare @CP int
    9. Declare @SV int
    10.  
    11. While @ListStr<>''
    12. Begin
    13.     Set @CP=CharIndex(',',@ListStr)
    14.     If @CP<>0
    15.     Begin
    16.         Set @SV=Cast(Left(@ListStr,@CP-1) as int)
    17.         Set @ListStr=Right(@ListStr,Len(@ListStr)-@CP)
    18.     End
    19.     Else
    20.     Begin
    21.         Set @SV=Cast(@ListStr as int)
    22.         Set @ListStr=''
    23.     End
    24.     Insert into @ListTbl Values (@SV)
    25. End
    26.  
    27. -- We have now built our "table variable" with a row for each "list item"
    28.  
    29. SELECT TOP 100 PERCENT
    30. code AS TA, code2 AS CT, ProjName AS Project, Phase, Protocol,
    31. fields, ProjectName AS [Study Name],
    32. CONVERT(char(10), POD_Op, 101) AS [POD],POD_Note AS [Comment]
    33. , CONVERT(char(10), Op, 101) AS [IND],Note AS [NComment]
    34. , CONVERT(char(10), POp, 101) AS [Approved],
    35. dbo.vwMSP_KPI_Singl_Sorted.PrtclA_Note AS [Protocol
    36.  
    37. FROM Fields LEFT OUTER JOIN PKSuppl2 ON
    38. PROJ_ID = ProjectUniqueID LEFT OUTER JOIN
    39. OutlineCodes ON Fields.PROJ_ID = PROJ_ID LEFT OUTER JOIN
    40. Sorted ON dbo.vwMSP_Project_Fields.PROJ_ID = ProjectUniqueID
    41.  
    42. [b]Where Id in (Select SelectValue From @ListTbl)[/b]
    43. -- Something like this might work
    44. 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".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  15. #15

    Thread Starter
    Addicted Member
    Join Date
    Nov 2002
    Posts
    150

    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
    Attached Images Attached Images  

  16. #16
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    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.

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

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  17. #17

    Thread Starter
    Addicted Member
    Join Date
    Nov 2002
    Posts
    150

    Re: stored procedure multi-select question

    thanks szlmany. problem is sloved.

    waely

  18. #18
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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.

  19. #19
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: [RESOLVED] stored procedure multi-select question

    Quote Originally Posted by MMock View 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.
    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".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  20. #20
    PowerPoster MMock's Avatar
    Join Date
    Apr 2007
    Location
    Driving a 2018 Mustang GT down Route 8
    Posts
    4,475

    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
  •  



Click Here to Expand Forum to Full Width