SQL stored Procedures

  • Hi All,

    Is there a way in creating stored procedure where you set parameters to accept multiple values, eg you want to set a parameter @DEPARTMENT, where when you execute the procs, you will have the option to enter more than one department name to generate the report

  • As you're posting it in the SQL 2008 forum, I'll assume that's the version you're running.

    In which case, Table Valued Parameters suit this requirement nicely:

    http://msdn.microsoft.com/en-us/library/bb510489.aspx

    If you're not using 2008 yet, your options are either have a fixed number of values allowed and create different parameters for each one which your front end will pass the values to, or send in a delimited list as a string then split the string out in the stored procedure.

  • Hi could you please explain the last part of your answer about the delimited list as a string

    Thanks

  • Relatively easy concept... have a character defined that acts as a separator.

    EG. ¦,;: could all be used

    IT,HR,Sales.... etc

    You will then need to LOOP through, pulling a value off each time as you know each value is separated by specific character.

  • Hi Thanks,

    Can I please send you the query so you modify it for me?

    Thanks

    The query is as follows

    USE [HQRMS]

    GO

    CREATE PROCEDURE [dbo].[sp_getSummarySalesReport_By_DeptTest]

    (@DATEFROM AS DATETIME,@DATETO AS DATETIME,@DEPARTMENT AS NVARCHAR(50))

    AS

    SELECT DEPARTMENT.NAME AS DEPARTMENT,CATEGORY.NAME AS CATEGORY,ITEM.DESCRIPTION AS DESCRIPTION,STRS.ID,([TRANSACTION].TIME) AS TIME,

    STRS.[NAME] AS STORENAME,SREGNS.NAME AS REGION, SUM(TRANSACTIONENTRY.QUANTITY)

    AS QUANTITY,SUM(TRANSACTIONENTRY.PRICE * TRANSACTIONENTRY.QUANTITY)

    AS TOTAL

    FROM TRANSACTIONENTRY

    INNER JOIN [TRANSACTION] WITH(NOLOCK)

    ON TRANSACTIONENTRY.TRANSACTIONNUMBER = [TRANSACTION].TRANSACTIONNUMBER AND TRANSACTIONENTRY.STOREID = [TRANSACTION].STOREID

    INNER JOIN BATCH WITH(NOLOCK)

    ON [TRANSACTION].BATCHNUMBER = BATCH.BATCHNUMBER AND [TRANSACTION].STOREID = BATCH.STOREID

    LEFT JOIN ITEM WITH(NOLOCK)

    ON TRANSACTIONENTRY.ITEMID = ITEM.ID

    LEFT JOIN DEPARTMENT WITH(NOLOCK)

    ON ITEM.DEPARTMENTID = DEPARTMENT.ID

    LEFT JOIN CATEGORY WITH(NOLOCK)

    ON ITEM.CATEGORYID = CATEGORY.ID

    LEFT JOIN SUPPLIER WITH(NOLOCK)

    ON ITEM.SUPPLIERID = SUPPLIER.ID

    LEFT JOIN CUSTOMER WITH(NOLOCK)

    ON [TRANSACTION].CUSTOMERID = CUSTOMER.ID

    LEFT JOIN REASONCODE AS REASONCODEDISCOUNT WITH(NOLOCK)

    ON TRANSACTIONENTRY.DISCOUNTREASONCODEID = REASONCODEDISCOUNT.ID

    LEFT JOIN REASONCODE AS REASONCODETAXCHANGE WITH(NOLOCK)

    ON TRANSACTIONENTRY.TAXCHANGEREASONCODEID = REASONCODETAXCHANGE.ID

    LEFT JOIN REASONCODE AS REASONCODERETURN WITH(NOLOCK)

    ON TRANSACTIONENTRY.RETURNREASONCODEID = REASONCODERETURN.ID

    LEFT JOIN STRS

    ON [TRANSACTION].STOREID = STRS.ID

    LEFT JOIN SREGNS

    ON STRS.SREGNID=SREGNS.ID

    LEFT JOIN CASHIER WITH(NOLOCK)

    ON [TRANSACTION].CASHIERID = CASHIER.ID AND STRS.ID = CASHIER.STOREID

    WHERE CONVERT(DATETIME,[TRANSACTION].TIME,103) BETWEEN CONVERT(DATETIME,@DATEFROM,103) AND CONVERT(DATETIME,@DATETO,103)

    AND STRS.[NAME] IS NOT NULL AND @DEPARTMENT IN(SELECT DEPARTMENT.NAME FROM DEPARTMENT)

    GROUP BY DEPARTMENT.NAME,CATEGORY.NAME,ITEM.DESCRIPTION,STRS.ID,STRS.NAME,SREGNS.NAME,[TRANSACTION].TIME

  • Well as stated, if SQL 2008, I would be looking at the Table Valued Parameters as suggested.

    Sorry, but this is your work, I am not going to do it, how else do you expect to learn.

  • OK will try as you have suggested and give you a feed back,

    but it is 2005 I am using so what do you suggest

    Thanks

  • BTW you may want to do some reading on naming (best) practises....

    sp_ tut tut :hehe:

  • Well you did post in the SQL2008 area, hence the advice given.

    It depends on the requirement. If you only need to cater for 3-4 departments, then I would go for additional parameters, if more than that, I would go for the separated string and split out the values in the procedure.

  • No I have about 10 departments

  • Here is why using the sp_ prefix for your proc names is a bad idea:

    http://blogs.msdn.com/b/jenss/archive/2009/04/28/a-long-but-not-missed-friend-revisited-prefixing-stored-procedures-with-sp.aspx

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • I have a function that may help you...of course, you'd have to use dynamic SQL (which has it's own caveats)

    It can be used nicely to return a set of values for the IN keyword in the WHERE clause

    Ex: SELECT dbo.fx_FormatArrayText('IT|HR|ACCOUNTING|SALES','|', 1)

    Returns:

    ('IT','HR','ACCOUNTING','SALES')

    If your @DEPARTMENT parameter has the list of departments then you can easily add that to your WHERE clause...something like:

    DECLARE @sql varchar(1000)

    DECLARE @DeptWhere varchar(100)

    SET @DeptWhere = dbo.fx_FormatArrayText(@DEPARTMENT,',', 1)

    SET @DeptWhere = ' AND S.DEPT IN ' + @DeptWhere

    SET @sql = '

    SELECT

    D.NAME

    ...

    ...

    FROM TRANSACTIONENTRY TR

    INNER JOIN [TRANSACTION] TX WITH(READUNCOMMITTED) ON

    TR.TRANSACTIONNUMBER = TX.TRANSACTIONNUMBER

    AND TR.STOREID = TX.STOREID

    ...

    ...

    WHERE

    CONVERT(DATETIME,TX.TIME,103) ...

    AND S.[NAME] IS NOT NULL

    ' + RTRIM(@DeptWhere) + '

    GROUP BY

    D.NAME, C.NAME, I.DESCRIPTION, S.ID,

    S.NAME, SR.NAME, TX.TIME '

    If you need to check your query just do a quick PRINT(@SQL), you can copy and paste that into another query window to check it.

    Here's the code for the scalar UDF:

    /* ############################################################################################################### */

    --FORMAT DELIMITED STRING

    /* ############################################################################################################### */

    CREATE FUNCTION fx_FormatArrayText (

    @String varchar(1500),

    @Delimiter char(1),

    @NumberQuotes int = 1)

    RETURNS varchar(1500) AS

    /*

    ----------------------------------------------------------------------------------------------------------------

    Purpose:Convert delimited text within a string into parenthesized values (quotes optional)

    Department:DB&R

    Created For:

    ----------------------------------------------------------------------------------------------------------------

    NOTES:@Delimiter - Tells function the delimiter to parse the text with

    @NumberQuotes - How many quotes you wisdh to have in the OUTPUT string

    ----------------------------------------------------------------------------------------------------------------

    Created On:10/20/2005

    Create By:Serge Mirault

    ----------------------------------------------------------------------------------------------------------------

    Modified On:

    Modified By:

    Changes:

    1.

    ----------------------------------------------------------------------------------------------------------------

    SELECT dbo.fx_FormatArrayText('IT|HR|ACCOUNTING|SALES','|', 1)

    */

    BEGIN

    DECLARE @Quote varchar(10)

    SET @Quote = ''

    /* ######################################### START MAIN FUNCTION HERE ########################################## */

    IF @NumberQuotes >= 1

    BEGIN

    SET @Quote = SPACE(@NumberQuotes)

    SET @Quote = REPLACE(@Quote, ' ', '''')

    END

    IF @Delimiter = ' '

    BEGIN

    /* Eliminate double spaces in text string */

    WHILE CHARINDEX(' ', RTRIM(@String)) <> 0

    BEGIN

    SET @String = REPLACE(@String, ' ', ' ')

    END

    END

    ELSE

    BEGIN

    /* Eliminate all spaces in text string */

    WHILE CHARINDEX(' ', RTRIM(@String)) <> 0

    BEGIN

    SET @String = REPLACE(@String, ' ', '')

    END

    END

    /* Convert supplied delimiter with open quotes, comma, and close quotes */

    SET @String = REPLACE(@String, @Delimiter, @Quote + ',' + @Quote)

    /* Add opening and closing quotes and parentheses */

    SET @String = '(' + @Quote + @String + @Quote + ')'

    /* ########################################## END MAIN END HERE ########################################### */

    RETURN @String

    END

    /*

    SELECT dbo.fx_FormatArrayText('IT|HR|ACCOUNTING|SALES','|', 1)

    */

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • opc.three (2/24/2011)


    Here is why using the sp_ prefix for your proc names is a bad idea:

    http://blogs.msdn.com/b/jenss/archive/2009/04/28/a-long-but-not-missed-friend-revisited-prefixing-stored-procedures-with-sp.aspx

    Hm, nice article find, OPC. Thank you.

    To the OP: You can also have a look on this site for the delimitedsplit8k function. A link to one of the versions is here:

    http://www.sqlservercentral.com/Forums/Topic943562-338-1.aspx#bm944589

    There's a more current version that Jeff developed but I don't have the link handy.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 13 posts - 1 through 12 (of 12 total)

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