stored procedure and parameter

  • the following is my sp. i have a parameter @company

    i want the data retrieved based upon the @company selection

    the values in the field are AAA for Alfred Dunner AAA for JC Penny (based upon specific accounts)

    if i want the parameter selection to retrieve the specific dataset, do i need to declar the value of @company in the beg of the sp?

    if so how do i do that?

    (@INVMNTH INT, @INVYR int, @Company varchar(3))

    AS

    if @Company ='Alfred Dunner'

    begin

    SELECT DISTINCT

    dbo.INVDHIST.INSEAD AS Season,

    dbo.INVDHIST.INSTYL AS Style,

    dbo.INVDHIST.INCLRD AS Color,

    dbo.INVDHIST.INVYRas INV_YR,

    dbo.INVDHIST.INVMONAS INV_MNTH,

    incus#,

    indivn,

    SUM(dbo.INVDHIST.INSDQ1) AS Per1,

    SUM(dbo.INVDHIST.INSDQ2) AS Per2,

    SUM(dbo.INVDHIST.INSDQ3) AS Per3,

    SUM(dbo.INVDHIST.INSDQ4) AS Per4,

    SUM(dbo.INVDHIST.INSDQ5) AS Per5,

    SUM(dbo.INVDHIST.INSDQ6) AS Per6,

    SUM(dbo.INVDHIST.INSDQ7) AS Per7,

    SUM(dbo.INVDHIST.INSDQ8) AS Per8,

    SUM(dbo.INVDHIST.INSDQ9) AS Per9,

    (SUM(dbo.INVDHIST.INSDQ1)+ SUM(dbo.INVDHIST.INSDQ2)+SUM(dbo.INVDHIST.INSDQ3)+SUM(dbo.INVDHIST.INSDQ4)+SUM(dbo.INVDHIST.INSDQ5)+ SUM(dbo.INVDHIST.INSDQ6)+SUM(dbo.INVDHIST.INSDQ7)+ SUM(dbo.INVDHIST.INSDQ8)+SUM(dbo.INVDHIST.INSDQ9)) as tot_per,

    dbo.SCALE.SCSZ1 AS SIZE_1,

    dbo.SCALE.SCSZ2 AS SIZE_2,

    dbo.SCALE.SCSZ3 AS SIZE_3,

    dbo.SCALE.SCSZ4 AS SIZE_4,

    dbo.SCALE.SCSZ5 AS SIZE_5,

    dbo.SCALE.SCSZ6 AS SIZE_6,

    dbo.SCALE.SCSZ7 AS SIZE_7,

    dbo.SCALE.SCSZ8 AS SIZE_8,

    dbo.SCALE.SCSZ9 AS SIZE_9,

    MIN(dbo.STYLEMST.SYFSZ1) AS ON_HND_1,

    MIN(dbo.STYLEMST.SYFSZ2) AS ON_HND_2,

    MIN(dbo.STYLEMST.SYFSZ3) AS ON_HND_3,

    MIN(dbo.STYLEMST.SYFSZ4) AS ON_HND_4,

    MIN(dbo.STYLEMST.SYFSZ5) AS ON_HND_5,

    MIN(dbo.STYLEMST.SYFSZ6) AS ON_HND_6,

    MIN(dbo.STYLEMST.SYFSZ7) AS ON_HND_7,

    MIN(dbo.STYLEMST.SYFSZ8) AS ON_HND_8,

    MIN(dbo.STYLEMST.SYFSZ9) AS ON_HND_9,

    MIN(dbo.STYLEMST.SYASZ1) AS ON_ORD_1,

    MIN(dbo.STYLEMST.SYASZ2) AS ON_ORD_2,

    MIN(dbo.STYLEMST.SYASZ3) AS ON_ORD_3,

    MIN(dbo.STYLEMST.SYASZ4) AS ON_ORD_4,

    MIN(dbo.STYLEMST.SYASZ5) AS ON_ORD_5,

    MIN(dbo.STYLEMST.SYASZ6) AS ON_ORD_6,

    MIN(dbo.STYLEMST.SYASZ7) AS ON_ORD_7,

    MIN(dbo.STYLEMST.SYASZ8) AS ON_ORD_8,

    MIN(dbo.STYLEMST.SYASZ9) AS ON_ORD_9,

    (MIN(dbo.STYLEMST.SYFSZ1)+MIN(dbo.STYLEMST.SYFSZ2) +MIN(dbo.STYLEMST.SYFSZ3)+MIN(dbo.STYLEMST.SYFSZ4)+MIN(dbo.STYLEMST.SYFSZ5)+MIN(dbo.STYLEMST.SYFSZ6)+MIN(dbo.STYLEMST.SYFSZ7)+MIN(dbo.STYLEMST.SYFSZ8)+MIN(dbo.STYLEMST.SYFSZ9)) as tot_on_hand,

    (MIN(dbo.STYLEMST.SYASZ1)+MIN(dbo.STYLEMST.SYASZ2)+MIN(dbo.STYLEMST.SYASZ3) +MIN(dbo.STYLEMST.SYASZ4) +MIN(dbo.STYLEMST.SYASZ5) + MIN(dbo.STYLEMST.SYASZ6)+MIN(dbo.STYLEMST.SYASZ7)+MIN(dbo.STYLEMST.SYASZ8)+MIN(dbo.STYLEMST.SYASZ9))as tot_on_ord,

    MIN(SYFSZ1 + SYASZ1 ) AVAIL1,

    MIN(SYFSZ2 + SYASZ2 ) AVAIL2,

    MIN(SYFSZ3 + SYASZ3 ) AVAIL3,

    MIN(SYFSZ4 + SYASZ4 ) AVAIL4,

    MIN(SYFSZ5 + SYASZ5 ) AVAIL5,

    MIN(SYFSZ6 + SYASZ6 ) AVAIL6,

    MIN(SYFSZ7 + SYASZ7 ) AVAIL7,

    MIN(SYFSZ8 + SYASZ8 ) AVAIL8,

    MIN(SYFSZ9 + SYASZ9 ) AVAIL9,

    (MIN(SYFSZ1 + SYASZ1 )+MIN(SYFSZ2 + SYASZ2 )+MIN(SYFSZ3 + SYASZ3 )+MIN(SYFSZ4 + SYASZ4 )+MIN(SYFSZ5 + SYASZ5 )+MIN(SYFSZ6 + SYASZ6 )+MIN(SYFSZ7 + SYASZ7 )+MIN(SYFSZ8 + SYASZ8 )+MIN(SYFSZ9 + SYASZ9 ))as tot_avail,

    case when syclas ='P' then 'MISSY'

    WHEN syclas ='K' then 'PETITE'

    WHEN syclas ='J' then 'WOMEN' END DIVISION,

    case when indivn ='AAA' then 'Alfred Dunner'

    when indivn ='AAA' and incus# in ('48700','48701','48702','48709') then 'JC Penny'

    when indivn ='CCC' then 'Hearts of Palm' end Company

    FROM

    dbo.INVDHIST

    INNER JOIN

    dbo.SCALE ON dbo.SCALE.SCCODE = dbo.INVDHIST.INSCLE

    INNER JOIN

    dbo.STYLEMST ON dbo.STYLEMST.SYSEA = dbo.INVDHIST.INSEAD

    AND dbo.STYLEMST.SYSTYL = dbo.INVDHIST.INSTYL

    AND dbo.STYLEMST.SYCOLR = dbo.INVDHIST.INCLOR

    where indivn ='AAA'

    ----- The selected Year and Month

    ----- INVYR = @INVYR and

    ----- INVMON = @INVMNTH

    ----- or

    ----- The Year Prior and the Selected Month

    INVYR = @INVYR -1 and

    INVMON = @INVMNTH

    or

    indivn ='AAA' and

    ----- The Year Prior and the Month + 1

    (

    INVYR = CASE

    WHEN @INVMNTH + 1 = 13

    THEN @INVYR

    ELSE @INVYR - 1

    END

    AND

    INVMON = CASE WHEN @INVMNTH + 1 = 13 THEN 1

    ELSE

    @INVMNTH +1

    END

    )

    or

    indivn ='AAA' and

    ----- The Year Prior and the Month + 2

    (

    INVYR = CASE

    WHEN @INVMNTH + 2 = 14 THEN @INVYR

    WHEN @INVMNTH + 2 = 13 THEN @INVYR

    ELSE @INVYR - 1

    END

    AND

    INVMON = CASE

    WHEN @INVMNTH + 2 = 14 THEN 2

    WHEN @INVMNTH + 2 = 13 THEN 1

    ELSE @INVMNTH +2

    END

    )

    or

    indivn ='AAA' and

    ----- The Year Prior and the Month + 3

    (

    INVYR = CASE

    WHEN @INVMNTH + 3 = 15 THEN @INVYR

    WHEN @INVMNTH + 3 = 14 THEN @INVYR

    WHEN @INVMNTH + 3 = 13 THEN @INVYR

    ELSE @INVYR - 1

    END

    AND

    INVMON = CASE

    WHEN @INVMNTH + 3 = 15 THEN 3

    WHEN @INVMNTH + 3 = 14 THEN 2

    WHEN @INVMNTH + 3 = 13 THEN 1

    ELSE @INVMNTH +3

    END

    )

    GROUP BY

    dbo.INVDHIST.INSEAD,

    dbo.INVDHIST.INSTYL,

    dbo.INVDHIST.INCLRD,

    dbo.INVDHIST.INVYR,

    dbo.INVDHIST.INVMON,

    dbo.SCALE.SCSZ1,

    dbo.SCALE.SCSZ2,

    dbo.SCALE.SCSZ3,

    dbo.SCALE.SCSZ4,

    dbo.SCALE.SCSZ5,

    dbo.SCALE.SCSZ6,

    dbo.SCALE.SCSZ7,

    dbo.SCALE.SCSZ8,

    dbo.SCALE.SCSZ9,

    dbo.STYLEMST.SYASZ1,

    dbo.STYLEMST.SYASZ2,

    dbo.STYLEMST.SYASZ3,

    dbo.STYLEMST.SYASZ4,

    dbo.STYLEMST.SYASZ5,

    dbo.STYLEMST.SYASZ6,

    dbo.STYLEMST.SYASZ7,

    dbo.STYLEMST.SYASZ8,

    dbo.STYLEMST.SYASZ9,

    SYFSZ1 ,

    SYFSZ2,

    SYFSZ3,

    SYFSZ4,

    SYFSZ5 ,

    SYFSZ6 ,

    SYFSZ7 ,

    SYFSZ8,

    SYFSZ9,

    syclas,

    INDIVN,

    incus#

    END

    else

    if @Company ='JC Penny'

  • If I understand your question correctly, you want to execute a specific query based upon the incoming parameter. If that's the case, sounds like you'll need to build a dynamic query using variables and sp_executesql.

    If you are going to do this, make sure you check the incoming parameters (either via the sending application of in the proc) for anything which would cause an injection attack.

  • DooDoo (7/22/2010)


    If I understand your question correctly, you want to execute a specific query based upon the incoming parameter. If that's the case, sounds like you'll need to build a dynamic query using variables and sp_executesql.

    If you are going to do this, make sure you check the incoming parameters (either via the sending application of in the proc) for anything which would cause an injection attack.

    I'm not sure you need a dynamic query, but it depends.

    As far as the OP, sometimes less is more. When the code is that long, maybe just a small subset is better. Anyway, Do you want run different queries based on Company paramter? If so then you could use something like this:

    create Procedure Usp_proc (@Company int)

    as

    Begin

    if @Company = 'A'

    begin

    select * from TableA where Company = @Company

    end

    if @Company = 'B'

    begin

    select * from TableB where Company = @Company

    end

    end

    For better, quicker answers, click on the following...
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • I'm not sure, but I'm thinking that you might be breaking up the statements into separate IF blocks to filter data rather than actually having a separate query. If that is the case, you probably don't need to do that ... it would be easier to modify the WHERE clause on the basis of the @company than to have completely separate statements. I could be more specific if you proved the full text of the proc with the other IF blocks.

    And in regards to the WHERE clause, I have a suggestion to clean that up ...

    First, put this line above the beginning of your select:

    DECLARE @myDate DATETIME = DATEADD(mm, @INVMNTH - 1, DATEADD(yy, @INVYR - 1900, 0))

    Then you can change your WHERE clause:

    select fields

    from table

    where indivn ='AAA' AND

    (

    ( -- The Actual Month Selected

    INVYR = @INVYR -1

    AND INVMON = @INVMNTH)

    OR

    ( -- The Year Prior and the Month + 1 (11 months prior)

    INVYR = DATEPART(yy,(DATEADD(mm, -11, @myDate)))

    AND INVMON = DATEPART(mm,(DATEADD(mm, -11, @myDate))))

    OR

    ( -- The Year Prior and the Month + 2 (10 months prior)

    INVYR = DATEPART(yy,(DATEADD(mm, -10, @myDate)))

    AND INVMON = DATEPART(mm,(DATEADD(mm, -10, @myDate))))

    OR

    ( -- The Year Prior and the Month + 3 (9 months prior)

    INVYR = DATEPART(yy,(DATEADD(mm, -9, @myDate)))

    AND INVMON = DATEPART(mm,(DATEADD(mm, -9, @myDate))))

    )

    This shouldn't be any different functionally. It's just easier to read and maintain.

    └> bt



    Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Viewing 4 posts - 1 through 3 (of 3 total)

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