July 22, 2010 at 12:25 pm
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.INVYR as INV_YR,
dbo.INVDHIST.INVMON AS 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'
July 22, 2010 at 12:35 pm
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.
July 22, 2010 at 12:41 pm
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/
July 22, 2010 at 2:45 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply