Scalar variable - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Scalar variable

Author

Topic

bvanyangu
Starting Member

20 Posts

Posted - 2014-06-21 : 03:24:59

Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@ReportDate".

I am Confused on what I need to do. I tried google, but don't exactly what i need to do or how do I declare that variable.

Select Distinct 'Age' DemographicGroup,
CASE WHEN DATEDIFF(YY,[Date of Birth],@ReportDate) < 25 THEN '<=25',
WHEN DATEDIFF(YY,[DATE of Birth],@ReportDate) between 25 and 35 THEN '26-35'
WHEN DATEDIFF(YY,[DATE of Birth],@ReportDate) between 36 and 45 THEN '36-45'
WHEN DATEDIFF(YY,[DATE of Birth],@ReportDate) > 45 THEN '46+' END as DemoGroup
WHEN DATEDIFF(YY,[DATE of Birth],@ReportDate) < 25 THEN 1
WHEN DATEDIFF(YY,[DATE of Birth],@ReportDate) between 25 and 35 THEN
WHEN DATEDIFF(YY,[DATE of Birth],@ReportDate) between 36 and 45 THEN 3
WHEN DATEDIFF(YY,[DATE of Birth],@ReportDate) > 45 THEN 4 END as DemoGroupOrder
Into #Demographic
From BorrowerDim_Staging
UNION
Select Distinct 'Marital Status',MaritalStatus,1
From BorrowerDim_Staging
UNION
Select Distinct 'Race',Race,1
From BorrowerDim_Staging
UNION
Select Distinct 'Sex',Sex,1
From BorrowerDim_Staging

SELECT *,DemographicGroup + ' - ' + DemoGroup DemoGrouplabel
FROM #Demographic
WHERE DemographicGroup IN @DemoCategory
ORDER BY DemographicGroup, DemoGroupOrder Desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-06-21 : 04:57:43

you need to declare it on the top and assign it some value


DECLARE @DemoCategory varchar(5000)
SET @DemoCategory = <put some comma separated value here>

Select Distinct 'Age' DemographicGroup,
CASE WHEN DATEDIFF(YY,[Date of Birth],@ReportDate) < 25 THEN '<=25',
WHEN DATEDIFF(YY,[DATE of Birth],@ReportDate) between 25 and 35 THEN '26-35'
WHEN DATEDIFF(YY,[DATE of Birth],@ReportDate) between 36 and 45 THEN '36-45'	
WHEN DATEDIFF(YY,[DATE of Birth],@ReportDate) > 45 THEN '46+' END as DemoGroup
WHEN DATEDIFF(YY,[DATE of Birth],@ReportDate) < 25 THEN 1
WHEN DATEDIFF(YY,[DATE of Birth],@ReportDate) between 25 and 35 THEN 
WHEN DATEDIFF(YY,[DATE of Birth],@ReportDate) between 36 and 45 THEN 3
WHEN DATEDIFF(YY,[DATE of Birth],@ReportDate) > 45 THEN 4 END as DemoGroupOrder
Into #Demographic	
From BorrowerDim_Staging
UNION
Select Distinct 'Marital Status',MaritalStatus,1
From BorrowerDim_Staging
UNION
Select Distinct 'Race',Race,1
From BorrowerDim_Staging
UNION
Select Distinct 'Sex',Sex,1
From BorrowerDim_Staging

SELECT *,DemographicGroup + ' - ' + DemoGroup DemoGrouplabel
FROM #Demographic
WHERE ',' + @DemoCategory + ',' LIKE '%,' + DemographicGroup + ',%'
ORDER BY DemographicGroup, DemoGroupOrder Desc

A much better option would be to replace LIKE statement with this


WHERE DemographicGroup IN (SELECT Val FROM bo.ParseValues(@DemoCategory,','))

which would require string parsing UDF as below

http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources