Forum Discussion
Parameterizing SQL Select
I'm new to Microsoft SQL Server:
I have this:
USE master
GO
CREATE PROCEDURE dbo.CreateTriangle @vCurMoNum varchar(2) @vCurYr5 varchar(4) @vCurYr varchar(4)
AS
BEGIN
SELECT
PREM.MCX_CD as MCX_CD,
case when DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) - DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) < 1 and
to_date(PREM.ACTG_YR_MO,'YYYYMM') <= to_date(DATEPART('YEAR', to_date(PREM.ACTG_YR_MO,'YYYYMM'))+0||'@vCurMoNum','YYYYMM')
@vCurYr-DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM'))>-1
then sum(PREM.EP_US_CURY_AMT) else null end as AY_EPCOL1,
case when DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) - DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) < 2 and
to_date(PREM.ACTG_YR_MO,'YYYYMM') <= to_date(DATEPART('YEAR', to_date(PREM.ACTG_YR_MO,'YYYYMM'))+1||'@vCurMoNum','YYYYMM') and
@vCurYr-DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM'))>0
then sum(PREM.EP_US_CURY_AMT) else null end as AY_EPCOL2,
case when DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) - DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) < 3 and
to_date(PREM.ACTG_YR_MO,'YYYYMM') <= to_date(DATEPART('YEAR', to_date(PREM.ACTG_YR_MO,'YYYYMM'))+2||'@vCurMoNum','YYYYMM') and
@vCurYr-DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM'))>1
then sum(PREM.EP_US_CURY_AMT) else null end as AY_EPCOL3,
case when DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) - DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) < 4 and
to_date(PREM.ACTG_YR_MO,'YYYYMM') <= to_date(DATEPART('YEAR', to_date(PREM.ACTG_YR_MO,'YYYYMM'))+3||'@vCurMoNum','YYYYMM') and
@vCurYr-DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM'))>2
then sum(PREM.EP_US_CURY_AMT) else null end as AY_EPCOL4,
case when DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) - DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) < 5 and
to_date(PREM.ACTG_YR_MO,'YYYYMM') <= to_date(DATEPART('YEAR', to_date(PREM.ACTG_YR_MO,'YYYYMM'))+4||'@vCurMoNum','YYYYMM') and
@vCurYr-DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM'))>3
then sum(PREM.EP_US_CURY_AMT) else null end as AY_EPCOL5,
sum(PREM.EP_US_CURY_AMT) as EP_US_CURY_AMT,
from PREMIUM PREM
LEFT JOIN MCX MCX
ON MCX.MCX_CD = PREM.MCX_CD
WHERE
PREM.ACTG_YR >= '@vCurYr5'
AND
PREM.ACTG_YR_MO <= '@vCurYr@vCurMoNum'
AND
MCX.MCX_LV_3_CD IN
('XXX','YYY')
and
PREM.INS_CD IN ('DIR', 'ASM')
group by
PREM.MCX_CD
Having sum(PREM.EP_US_CURY_AMT) <> 0 ;
END
Can I simplify doing this:
USE master
GO
CREATE PROCEDURE dbo.CreateTriangle @vCurMoNum varchar(2) @vCurYr5 varchar(4) @vCurYr varchar(4)
AS
BEGIN
SELECT
PREM.MCX_CD as MCX_CD,
BEGIN
DECLARE
@X int= 1
@Y int=0
@Z int=0
WHILE @X< @vCurMoNum
BEGIN
SET @Y=@X - 2
SET @Z=@X – 1
case when DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) - DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM')) < @X and
to_date(PREM.ACTG_YR_MO,'YYYYMM') <= to_date(DATEPART('YEAR', to_date(PREM.ACTG_YR_MO,'YYYYMM'))+@Z||'@vCurMoNum','YYYYMM')
@vCurYr-DATEPART('YEAR',to_date(PREM.ACTG_YR_MO,'YYYYMM'))>@Y
then sum(PREM.EP_US_CURY_AMT) else null end as AY_EPCOL@X,
SET @X= @X + 1
END
END
sum(PREM.EP_US_CURY_AMT) as EP_US_CURY_AMT
from PREMIUM PREM
LEFT JOIN MCX MCX
ON MCX.MCX_CD = PREM.MCX_CD
WHERE
PREM.ACTG_YR >= '@vCurYr5'
AND
PREM.ACTG_YR_MO <= '@vCurYr@vCurMoNum'
AND
MCX.MCX_LV_3_CD IN
('XXX','YYY')
and
PREM.INS_CD IN ('DIR', 'ASM')
group by
PREM.MCX_CD
Having sum(PREM.EP_US_CURY_AMT) <> 0 ;
END
Excuting the Procedure using this:
USE master
GO
EXEC dbo.CreateTriangle '06', '2017', '2022'
If not, what can I do to simplify this way
1 Reply
Do you know the meaning of the master database?
I highly recommend you to stop all you do as soon as you can, until you fully understand, since you are working with the master database and anything you do can have a huge impact on your entire server including all databases!
The master database is a system database which records all the system-level information for a SQL Server system. You should not use it for storing your data!
According your code you are using the master database as it was a user database. Your first step should be to create a database and work only on your user databases (databases which you create)
Regarding the code
(1) I did not read the entire code since there is no reason for it before you provide the information to execute it, but I did noticed an issue in the first line. When you create SP then the variable should be separated with comma
(2) To be able to execute your code we need to have the relevant objects. please provide:
- Queries to CREATE your table(s) including indexes
- Queries to INSERT sample data.
- The desired result given the sample, as text or image of excel for example.
- A short description of the business rules, and how you got 1-2 of the results
- Which version of SQL Server you are using (this will help to fit the query to your version).
Regards,
