using all in a parameter in a stored procedure

  • i have a stored procedure where i want to use parameters, i want the user to be able to select more than one value in the parameter

    what would my syntax be

    i would also like the user to select ALL records

  • If you're using SQL Server 2008 you could pass the values as a table variable parameter.

    An example is given here.

    Regarding "ALL records": it depends how you pass the data to the procedure: if you "convert" ALL into a list of all paramters prior to calling the proc ten it shouldn't matter. But if you want to pass the value "ALL" to the procedure and return every row you could either use an IF ELSE clause to call two different queries within your sp or use a CASE statement. Hard to tell which version would work best for your scenario based on the info provided so far...



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • I do this frequently.

    I'll declare the input parameter as

    @ParameterName VARCHAR(4000)

    Then, whatever is calling the procedure needs to pass a comma delimited string of values. Example "1,2,3,4,5,6".

    In the procedure, you can then use a function to split the values out into a table for processing such as this one: http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx

    Hope that helps.

    Chris

  • chris-736523 (6/30/2010)


    I do this frequently.

    I'll declare the input parameter as

    @ParameterName VARCHAR(4000)

    Then, whatever is calling the procedure needs to pass a comma delimited string of values. Example "1,2,3,4,5,6".

    In the procedure, you can then use a function to split the values out into a table for processing such as this one: http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx

    Hope that helps.

    Chris

    Cool idea. Never thought this way before. SQLServer allows multiple parameters? Should be one input/output parameter only. Comma separated string on input and the same on output (first item would be returned value):-D

    If you want to pass multiple independant values pass it as individual appropriately typed parameters.

    If you want to pass multiple occurances of the above, do it in XML or, in SQL2008 as parameter of a table type, as advised by Lutz.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • chris-736523 (6/30/2010)


    I do this frequently.

    I'll declare the input parameter as

    @ParameterName VARCHAR(4000)

    Then, whatever is calling the procedure needs to pass a comma delimited string of values. Example "1,2,3,4,5,6".

    In the procedure, you can then use a function to split the values out into a table for processing such as this one: http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx

    Hope that helps.

    Chris

    I don't recommend using the script mentioned by Chris. There are solutions available that perform MUCH better than a *caugh* while loop.

    The best one I've seen so far is by Jeff Moden and posted here. The basic concept is described in the TallyTable article referenced in my signature. As you might notice the code changed between the article and the post I referenced. The reason is rather simple: to make a fast solution even faster.:-D (@Jeff: Yet another advert to your script and article. Raachinggg! :-D)



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • thank you, these are my parameters

    ALTER PROCEDURE [dbo].[CR_CUSTVOL]

    (@SGYR INT)

    --@REGION INT,

    --@ACCOUNT varchar(20))

    --@SEASON Varchar(2),

    --@SALESPERSON INT,

    --@STORE INT)

    this is my where statement, i dont know the exact syntax, im not a newbie, but still learning from all you pros.

    WHERE SGYR=@SGYR

    AND REGION = @REGION

    AND SEASON = @season

    AND SALESPERSON_CODE = @SALESPERSON

    AND STORE = @STORE

    do i need to declare all somewhere, use like instead of = to retrieve multiple values instead of a discrete one?

    thanks:-D

  • You can use the following in your WHERE clause:

    WHERE (SGYR = @SGYR OR @SGYR IS NULL)

    AND (REGION = @REGION OR @REGION IS NULL)

    AND (SEASON = @season OR @season IS NULL)

    AND (SALESPERSON_CODE = @SALESPERSON OR @SALESPERSON IS NULL)

    AND (STORE = @STORE OR @STORE IS NULL)

    You will need to declare all parameters.

    All records will be returned if all input parameres are set to NULL. Parameters set to non-null value (and any combination of such) will be used for filtering.

    PLEASE NOTE: The above syntax does look elegant but it will guarantee table or clusterd index scan, which negatively affect the query performance. You should test if its performance is acceptable in your case.

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • The table i am quering has over 70000 records, so all is acceptable on some. different thought

    i want to return the data set based upon the parameter selection - how do i begin the next set

    AS

    IF @DIVISION IN('1','2','3')

    BEGIN

    SELECT

    STATUS,

    SALESPERSON_CODE,

    SALESPERSON_NAME,

    DIVISION,

    ACCOUNT,

    STORE,

    SEASON,

    SGYRAS YR,

    SGDSCAS GRP,

    REGION,

    CUST_NAME,

    SELL_GRPNAME,

    PRICE,

    (CASE WHEN PRICE>=SYPRC1 THEN SYPRC1 END) AS REG_PRICE,

    (CASE WHEN PRICE=SYOFFP THEN SYOFFP END) AS O_P_PRICE,

    (CASE WHEN PRICE<SYPRC1 AND PRICE<>SYOFFP THEN PRICE END) AS SALES_PRICE,

    SUM(CASE WHEN DIVISION='MISSY' THEN UNITS END) AS MISSY,

    SUM(CASE WHEN DIVISION='WOMENS' THEN UNITS END)AS WOMENS,

    SUM(CASE WHEN DIVISION='PETITE' THEN UNITS END) AS PETITE,

    SUM(CASE WHEN DIVISION='MISSY' AND PRICE>=SYPRC1 THEN UNITS END) AS REG_MISSY_UNITS,

    SUM(CASE WHEN DIVISION='MISSY' AND PRICE=SYOFFP AND SYOFFP <>SYPRC1 THEN UNITS END) AS OP_MISSY_UNITS,

    SUM(CASE WHEN DIVISION='MISSY' AND PRICE<SYPRC1 AND PRICE<>SYOFFP THEN UNITS END) AS SP_MISSY_UNITS,

    SUM(CASE WHEN DIVISION='WOMENS' AND PRICE>=SYPRC1 THEN UNITS END) AS REG_WOMENS_UNITS,

    SUM(CASE WHEN DIVISION='WOMENS' AND PRICE=SYOFFP AND SYOFFP <>SYPRC1 THEN UNITS END) AS OP_WOMENS_UNITS,

    SUM(CASE WHEN DIVISION='WOMENS' AND PRICE<SYPRC1 AND PRICE<>SYOFFP THEN UNITS END) AS SP_WOMENS_UNITS,

    SUM(CASE WHEN DIVISION='PETITE' AND PRICE>=SYPRC1 THEN UNITS END) AS REG_PETITE_UNITS,

    SUM(CASE WHEN DIVISION='PETITE' AND PRICE=SYOFFP AND SYOFFP <>SYPRC1 THEN UNITS END) AS OP_PETITE_UNITS,

    SUM(CASE WHEN DIVISION='PETITE' AND PRICE<SYPRC1 AND PRICE<>SYOFFP THEN UNITS END) AS SP_PETITE_UNITS

    FROM

    (SELECT ORSTAT STATUS,ORSTYL STYLE,ORCLOR,ORNUMD ORDER_NO,ORSEQD ORDER_LINE,

    ORSIZ1,ORSIZ2, ORSIZ3, ORSIZ4, ORSIZ5, ORSIZ6, ORSIZ7,ORSIZ8,ORSIZ9, ORACCT ACCOUNT,ORSTRP STORE,ORSEAA SEASON,

    ORSMN SALESPERSON_CODE,SMNNAM SALESPERSON_NAME,CMROUT REGION, CMBNAM CUST_NAME,B.SGYR,B.SGDSC,GRDESC SELL_GRPNAME,ORPRTY,

    CASE WHEN ORPRTY IN (1,2) then 'MISSY'

    WHEN ORPRTY IN (6,7) THEN 'PETITE'

    WHEN ORPRTY IN (3,4) THEN 'WOMENS'

    END DIVISION,

    ORPRCE PRICE,SYPRC1,SYOFFP,

    (CASE WHEN ORPRCE>=SYPRC1 THEN SYPRC1 END) AS REG_PRICE,

    (CASE WHEN ORPRCE=SYOFFP AND SYOFFP<>SYPRC1 THEN SYOFFP END) AS O_P_PRICE,

    (CASE WHEN ORPRCE<SYPRC1 AND ORPRCE<>SYOFFP THEN ORPRCE END) AS SALES_PRICE,

    ORSIZ1+ORSIZ2+ORSIZ3+ ORSIZ4+ ORSIZ5+ ORSIZ6+ ORSIZ7+ORSIZ8+ORSIZ9 UNITS

    FROM OORDDETL

    JOIN OORDHDRB

    ON ORNUMD=ORNUMB

    JOIN OORDHDRA

    ON ORNUMD=ORNUMA

    JOIN SALESPER

    ON ORSMN=SMNCOD

    JOIN STYLEMST

    ON ORSEAA=SYSEA

    AND ORSTYL=SYSTYL

    AND ORCLOR=SYCOLR

    JOIN CUSTMSTR

    ON ORACCT=CMBILL

    AND 000=CMSTOR

    JOIN

    (SELECT

    SGCOD, SGYR, SGDSC, SEASON, SEA_COLUMN

    FROM

    (SELECT SGCOD, SGYR, SGDSC, SGSE01 SEASON, 'SGSE01' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE02 SEASON, 'SGSE02' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE03 SEASON, 'SGSE03' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE04 SEASON , 'SGSE04' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE05 SEASON, 'SGSE05' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE06 SEASON, 'SGSE06' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE07 SEASON, 'SGSE07' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE08 SEASON, 'SGSE08' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE09 SEASON, 'SGSE09' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE10 SEASON, 'SGSE10' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE11 SEASON, 'SGSE11' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE12 SEASON, 'SGSE12' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE13 SEASON, 'SGSE13' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE14 SEASON, 'SGSE14' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE15 SEASON, 'SGSE15' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE16 SEASON, 'SGSE16' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE17 SEASON, 'SGSE17' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE18 SEASON, 'SGSE18' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE19 SEASON, 'SGSE19' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE20 SEASON, 'SGSE20' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE21 SEASON, 'SGSE21' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE22 SEASON, 'SGSE22' SEA_COLUMN FROM SELLGRPA

    UNION

    SELECT SGCOD, SGYR, SGDSC, SGSE23 SEASON, 'SGSE23' SEA_COLUMN FROM SELLGRPA

    )SELL_GRP

    WHERE SEASON<>' '

    ) B

    ON

    ORSEAA=SEASON

    JOIN GRPTAB

    ON SEASON=GRSEA

    AND SGYR=GRYR

    AND ORSTAT IN ('','S')) SALES

    WHERE UNITS<>0 --AND ACCOUNT='6360' AND SGYR='10'

    AND

    SGYR = @YEAR AND

    ACCOUNT= @account AND

    SALESPERSON_CODE=@SALESPERSON_NUMBER AND

    REGION= @REGION

    AND DIVISION=@DIVISION

    GROUP BY STATUS,

    PRICE,SALESPERSON_CODE,SALESPERSON_NAME,DIVISION,ACCOUNT,

    STORE, SEASON, SGYR ,SGDSC,

    REGION, CUST_NAME,SELL_GRPNAME, SYPRC1,SYOFFP

    order by store

    OR

    @DIVISION ='1'

    BEGIN

    SELECT

    STATUS,

  • I don't understand your question. Are you asking how to use IF ... ELSE in SQL or what?

    Just in case: http://msdn.microsoft.com/en-us/library/ms182587.aspx

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • lmu92 (6/30/2010)


    chris-736523 (6/30/2010)


    I do this frequently.

    I'll declare the input parameter as

    @ParameterName VARCHAR(4000)

    Then, whatever is calling the procedure needs to pass a comma delimited string of values. Example "1,2,3,4,5,6".

    In the procedure, you can then use a function to split the values out into a table for processing such as this one: http://blogs.microsoft.co.il/blogs/itai/archive/2009/02/01/t-sql-split-function.aspx

    Hope that helps.

    Chris

    I don't recommend using the script mentioned by Chris. There are solutions available that perform MUCH better than a *caugh* while loop.

    The best one I've seen so far is by Jeff Moden and posted here. The basic concept is described in the TallyTable article referenced in my signature. As you might notice the code changed between the article and the post I referenced. The reason is rather simple: to make a fast solution even faster.:-D (@Jeff: Yet another advert to your script and article. Raachinggg! :-D)

    lmu92,

    I tested both versions in question above. Both return more than 1000 values in less than 1 second. If you want to split hairs, can you provide an example of what clearly shows the "MUCH better" performance please? I'm interested in the difference from your perspective *COUGH* (from up there on your very tall horse).

    🙂 Now I'm just being argumentative and difficult, which I find quite fun.

    I agree that while loops are not the best way to code, and thank you for the example of better code, regardless of performance gains (or possible lack thereof). You should really do something about that cough though. 🙂

    Chris

  • thanks but i figured it out. i dont need a book, but thanks for the input

  • chris-736523 (7/1/2010)


    ...

    lmu92,

    I tested both versions in question above. Both return more than 1000 values in less than 1 second. If you want to split hairs, can you provide an example of what clearly shows the "MUCH better" performance please? I'm interested in the difference from your perspective *COUGH* (from up there on your very tall horse).

    🙂 Now I'm just being argumentative and difficult, which I find quite fun.

    I agree that while loops are not the best way to code, and thank you for the example of better code, regardless of performance gains (or possible lack thereof). You should really do something about that cough though. 🙂

    Chris

    Testing against 1000 rows might not be enough to see the difference. And, more important, might not represent the number of rows to be processed in most of the databases. Testing against a million rows with random character a string length of more than 7k character will definitely show the difference.

    Instead of providing the test code I'd rather point you at the Tally Table article like I did before. This excellent article includes a test setup for the million row test as well as the method on how to measure it.

    A common answer is "I neither have to split a string of such a length nor that large number of rows." My answer usually is: "But wouldn't it be nice to have a solution in place if you're faced with such a solution? Why would you use a solution that probably need to be redesigned to deal with an increased data volume in the first place?"

    Just think about what the two phrases "use a while loop" and "it'll take a while" have in common 😀

    Regarding that cough: there's nothing I can do about. But there's something you can do: stop using a while loop *cough* 😉

    So, instead of taking a performance discussion down to a personal level (like you did by mentioning the horse stuff) you might want to do some reading (e.g. the article I mentioned earlier).

    As a side note: when dealing with 1000 rows only, I wouldn't even consider to measure it in seconds....



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

  • lmu92 (6/30/2010)


    The best one I've seen so far is by Jeff Moden and posted here.

    Interesting. Even though I am credited on that post with a couple of optimisations, it is not my routine of choice. It performs very well for smaller sets but a SQLCLR routine wins in the majority of cases.

    Performance comparisons: http://florianreischl.blogspot.com/2009/09/high-performance-string-split-functions.html

    SQLCLR code by Adam Machanic: http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

    As far as the original requirement in this thread is concerned, passing a delimited string is just one of many options - and not necessarily a great one.

    The whole topic of passing arrays/lists around and writing queries with dynamic search conditions is explained in depth on Erland Sommarskog's site: http://www.sommarskog.se/

    Paul

  • Paul White NZ (7/2/2010)


    ...

    Interesting. Even though I am credited on that post with a couple of optimisations, it is not my routine of choice. It performs very well for smaller sets but a SQLCLR routine wins in the majority of cases.

    ...

    From my experience, CLR implementation outplays SQL UDF in most of cases (if not all). In T-SQL you cannot do as much optimization for the given task on the lower level as in C# (especially string manipulations). I doubt that any reasonable UDF can be faster than its proper CLR imlementaion (excl. functions which require other table lookups).

    I am aware that Jeff is not big fun of CLR's (he did mentioned it in one of the thread), but preferences change over the time. I remember, long ago, I didn't like using JOIN keywords instead of =, *= and =* 😀

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

  • Paul White NZ (7/2/2010)


    lmu92 (6/30/2010)


    The best one I've seen so far is by Jeff Moden and posted here.

    Interesting. Even though I am credited on that post with a couple of optimisations, it is not my routine of choice. It performs very well for smaller sets but a SQLCLR routine wins in the majority of cases.

    Performance comparisons: http://florianreischl.blogspot.com/2009/09/high-performance-string-split-functions.html

    SQLCLR code by Adam Machanic: http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx

    As far as the original requirement in this thread is concerned, passing a delimited string is just one of many options - and not necessarily a great one.

    The whole topic of passing arrays/lists around and writing queries with dynamic search conditions is explained in depth on Erland Sommarskog's site: http://www.sommarskog.se/

    Paul

    I probably should rephrase it to "The best one I've seen so far using T-SQL..." 😉



    Lutz
    A pessimist is an optimist with experience.

    How to get fast answers to your question[/url]
    How to post performance related questions[/url]
    Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply