[RESOLVED] stored procedure multi-select question-VBForums
Results 1 to 17 of 17

Thread: [RESOLVED] stored procedure multi-select question

Hybrid View

  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
    CT
    Posts
    15,558

    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 12: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
    CT
    Posts
    15,558

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

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,558

    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

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

  8. #8

    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

  9. #9

    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

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,558

    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

  11. #11

    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

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

  13. #13
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    15,558

    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

  14. #14

    Thread Starter
    Addicted Member
    Join Date
    Nov 2002
    Posts
    150

    Re: stored procedure multi-select question

    thanks szlmany. problem is sloved.

    waely

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width

Survey posted by VBForums.