Solved

SQL Server Database Error. Must Declare Scaler Variable

Posted on 2013-02-05
7
830 Views
Last Modified: 2013-02-06
Hey Guys,

I'm trying to get run this SP, but keep on getting this error:
Lookup Error - SQL Server Database Error: Must declare the scalar variable "@Y_YEAR".

The report works fine if I use 2012, but if I use 2013, I get this error.

Can you guys shed some light.

Thanks

--Declare @Acct_Unit as varchar(20)
Declare @Month as int
Declare @Year as char(4)
Declare @Month1 as varchar(25)
Declare @Month2 as varchar(25)
Declare @Month3 as varchar(25)
Declare @CY_Year as varchar(4)
Declare @PY_Year as varchar(4)
Declare @Act_YTD as varchar(1000)
Declare @Bud_YTD as varchar(1000)
Declare @Act_YTD_Units as varchar(1000)
Declare @MonthNumber1 as varchar(10)
Declare @LastDay as varchar(20)
/*Declare @Year as int
Declare @Month as int
----''21200'',''72000'')*/
SET @Y_YEAR = CAST(@Y_YEAR AS VARCHAR(4))
IF @Y_YEAR IS NULL OR @Y_YEAR = '' BEGIN set @Year = (select year(dateadd(mm,-1,getdate()))) END ELSE SET @Year = @Y_YEAR
IF @M_MONTH IS NULL OR @M_MONTH = '' BEGIN set @Month = (select month(dateadd(mm,-1,getdate())))END ELSE SET @Month = @M_MONTH


Declare @SQL1 AS VARCHAR(5000)
Declare @SQL2 AS VARCHAR(5000)
Declare @SQL3 as varchar(5000)
Declare @SQL9 AS VARCHAR(8000)

Declare @AUcnt as int
Declare @AUmax as int


--SELECT * FROM RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY

set @AUcnt=1
set @AUmax =(select max(PRT_ORDER) from RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY )

set @CY_Year = @Year
set @PY_Year = @Year-1
set @MonthNumber1 = (SELECT CASE WHEN floor(@Month) < = 9 THEN '0' + cast(floor(@Month) as varchar(1)) ELSE CAST(floor(@Month) AS VARCHAR(2)) END)
+'/1/' + cast(@Year as varchar(4))

set @Month1 = 'CR_AMOUNT_'
+left(CONVERT(char(10),@MonthNumber1,101),2)
+'+DB_AMOUNT_'
+left(CONVERT(char(10),@MonthNumber1,101),2)

set @Month2 = 'CR_AMOUNT_'
+left(CONVERT(char(10),dateadd(mm,-1,@MonthNumber1),101),2)
+'+DB_AMOUNT_'
+left(CONVERT(char(10),dateadd(mm,-1,@MonthNumber1),101),2)

set @Month3 =  'CR_AMOUNT_'
+left(CONVERT(char(10),dateadd(mm,-2,@MonthNumber1),101),2)
+'+DB_AMOUNT_'
+left(CONVERT(char(10),dateadd(mm,-2,@MonthNumber1),101),2)

SET @Act_YTD = RTRIM(REPLACE(REPLACE((select RTRIM(CR_COLUMN)+'+'+RTRIM(DB_COLUMN) FROM dbo.RPT_YTDCOLUMNS WHERE [MONTH] = @Month),'CR','A.CR'),'DB','A.DB'))
SET @Bud_YTD = RTRIM(REPLACE(REPLACE((select RTRIM(CR_COLUMN)+'+'+RTRIM(DB_COLUMN) FROM dbo.RPT_YTDCOLUMNS WHERE [MONTH] = @Month),'CR','B.CR'),'DB','B.DB'))
SET @Act_YTD_Units = REPLACE(REPLACE((SELECT RTRIM(CR_UNITS)+'+'+ RTRIM(DB_UNITS) FROM dbo.RPT_YTDCOLUMNS WHERE [MONTH] = @Month),'CR','U.CR'),'DB','U.DB')
DELETE  FROM RPT_INDIVIDUAL_BURST

while @AUcnt <= @AUmax
begin

-------------------------- separate accounting units -----------------------------
Declare @previous as int
declare @p as varchar(100)
Declare @pt as int
Declare @SQL_Acct as varchar(1000)
Declare @SQL_Acct1 as varchar(1000)
Declare @lastrec as varchar(100)

set @Year = cast(@Year as int)
set @LastDay = (select CAST(DAY(MAX([DATE]))AS VARCHAR(2)) FROM dbo.RPT_BGT_WORKDAYS where FISCAL_YEAR = @Year AND [MONTH]=floor(@Month))

set @previous = 0
set @p = ''
set @pt = 0
set @lastrec = ''

IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID('tempdb.dbo.#T_Acct')
)
BEGIN
DROP TABLE tempdb.#T_Acct
END
Declare @title as varchar(255)
set @title=''

create table tempdb.#T_Acct(Acct_Unit varchar(15) collate Latin1_General_BIN)
while charindex(',',(select ACCT_UNIT from dbo.RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY WHERE PRT_ORDER =@AUcnt),@pt+1)>0
begin
set @previous = @pt
set @pt = charindex(',',(select ACCT_UNIT from RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY WHERE PRT_ORDER =@AUcnt),(@pt+1))
if @pt>=0
begin
set @p = cast(ltrim(rtrim(substring((select ACCT_UNIT from RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY WHERE PRT_ORDER =@AUcnt),(@previous+1),((@pt-@previous)-1)))) as varchar)
set @previous = (@pt-@previous)-1
set @SQL_Acct = 'insert into tempdb.#T_Acct (Acct_Unit) Select LTRIM(RTRIM('''+@p +'''))'
exec(@SQL_Acct)
set @title= space(1)+isnull(@title,'')+ isnull((SELECT top 1 space(2)+ cast(ACCT_UNIT as varchar(5))+space(1)+ltrim(rtrim(DESCRIPTION)) as description  
FROM LSPROD.dbo.GLNAMES
where LEVEL_DEPTH = 4
and ACCT_UNIT = + ltrim(rtrim(@p))order by COMPANY asc
)+'','')
end
end
--last record

set @lastrec = right(ltrim(rtrim((select ACCT_UNIT from RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY WHERE PRT_ORDER =@AUcnt))),(len(ltrim(rtrim((select ACCT_UNIT from RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY WHERE PRT_ORDER =@AUcnt))))-@pt))
set @SQL_Acct1 = 'insert into tempdb.#T_Acct (Acct_Unit) select LTRIM(RTRIM('''+@lastrec+'''))'
exec (@SQL_Acct1)
set @title= space(1)+isnull(@title,'')+ isnull((SELECT TOP 1 space(2)+cast(ACCT_UNIT as varchar(5))+ltrim(rtrim(DESCRIPTION)) as description  
FROM LSPROD.dbo.GLNAMES
where LEVEL_DEPTH = 4
and ACCT_UNIT = + ltrim(rtrim(@lastrec))order by COMPANY asc
),'')

SET @SQL1 = 'SELECT A.ACCOUNT,A.FISCAL_YEAR,A.SUB_ACCOUNT,E.[GROUPING],E.ACCOUNT_DESC_ERDETAIL  AS ACCOUNT_DESC
           , SUM(A.'+ REPLACE(@Month1,'DB','A.DB') + ')  AS ACTM1
           , SUM(A.'+ REPLACE(@Month2,'DB','A.DB') + ') AS ACTM2
           , SUM(A.'+ REPLACE(@Month3,'DB','A.DB') + ') AS ACTM3
           , SUM(CASE WHEN A.FISCAL_YEAR IN ('+ @CY_Year+ ') THEN ' + @Act_YTD + ' ELSE 0 END ) AS ACT_CYTD
           , SUM(CASE WHEN A.FISCAL_YEAR IN ('+ @PY_Year+ ') THEN ' + @Act_YTD + ' ELSE 0 END ) AS ACT_PYTD
           ,0 AS BUDGETM1,0 AS BUDGET_YTD,'+@LastDay +' AS MONTH_LASTDAY
           FROM ER_ACCOUNTS E  
           inner join LSPROD.dbo.GLAMOUNTS A ON A.ACCOUNT = E.ACCOUNT  AND A.SUB_ACCOUNT = E.SUB_ACCOUNT WHERE '
           + case when @AUcnt=45 then '(A.ACCT_UNIT IN (Select * from tempdb.#T_Acct) and A.COMPANY = ''16'')'
           
            ELSE
           
          + ' A.ACCT_UNIT IN ( Select * from tempdb.#T_Acct)'
           END
           + 'AND A.FISCAL_YEAR IN ('+ @PY_Year + ',' + @CY_Year + ')AND A.ACCOUNT <> 4000
           GROUP BY A.ACCOUNT,A.FISCAL_YEAR,A.SUB_ACCOUNT,E.[GROUPING],E.ACCOUNT_DESC_ERDETAIL'
           
           
SET @SQL2 = 'SELECT B.ACCOUNT, B.FISCAL_YEAR,B.SUB_ACCOUNT,E.[GROUPING],E.ACCOUNT_DESC_ERDETAIL  AS ACCOUNT_DESC
          ,0,0,0,0,0 , SUM( CASE WHEN B.ACCOUNT =4000 THEN '+ REPLACE(@Month1,'AMOUNT','UNITS') +'
              ELSE B.'+ REPLACE(@Month1,'DB','B.DB') + ' END )AS BUDGETM1
          , SUM(CASE WHEN B.FISCAL_YEAR IN ('+ @CY_Year+ ') THEN   CASE WHEN B.ACCOUNT =4000 THEN '+ REPLACE(@Bud_YTD,'AMOUNT','UNITS')+'
               ELSE '+ @Bud_YTD + ' END ELSE 0 END )AS BUDGET_YTD ,'+@LastDay +' AS MONTH_LASTDAY
               FROM ER_ACCOUNTS E inner join LSPROD.dbo.FBDETAIL B     ON B.ACCOUNT = E.ACCOUNT AND B.SUB_ACCOUNT = E.SUB_ACCOUNT
           WHERE'+
            + case when @AUcnt=45 then '(B.ACCT_UNIT IN (Select * from tempdb.#T_Acct) and B.COMPANY = ''16'')'
           
           ELSE
           
          + ' B.ACCT_UNIT IN ( Select * from tempdb.#T_Acct)'
           END
           + ' AND B.FISCAL_YEAR IN ('+ @PY_Year + ',' + @CY_Year + ') AND B.BUDGET_NBR = 1
           group by B.ACCOUNT,B.FISCAL_YEAR,B.SUB_ACCOUNT,E.[GROUPING],E.ACCOUNT_DESC_ERDETAIL'
           
   
    SET @SQL3 = 'SELECT U.ACCOUNT,U.FISCAL_YEAR,U.SUB_ACCOUNT,E.[GROUPING],E.ACCOUNT_DESC_ERDETAIL  AS ACCOUNT_DESC
           , SUM('+ REPLACE(REPLACE(@Month1,'DB_AMOUNT','U.DB_UNITS'),'CR_AMOUNT','U.CR_UNITS') + ')  AS ACTM1
           , SUM('+ REPLACE(REPLACE(@Month2,'DB_AMOUNT','U.DB_UNITS'),'CR_AMOUNT','U.CR_UNITS') + ') AS ACTM2
           , SUM('+ REPLACE(REPLACE(@Month3,'DB_AMOUNT','U.DB_UNITS'),'CR_AMOUNT','U.CR_UNITS') + ') AS ACTM3
           , SUM(CASE WHEN U.FISCAL_YEAR IN ('+@CY_Year + ') THEN ' + @Act_YTD_Units + ' ELSE 0 END ) AS ACT_YTD
           , SUM(CASE WHEN U.FISCAL_YEAR IN ('+@PY_Year + ')THEN ' + @Act_YTD_Units + ' ELSE 0 END ) AS ACT_PYTD
           ,0,0,'+@LastDay +' AS MONTH_LASTDAY
           FROM ER_ACCOUNTS E  
           inner join LSPROD.dbo.GLUNITS U
           ON U.ACCOUNT = E.ACCOUNT
           AND U.SUB_ACCOUNT = E.SUB_ACCOUNT
          WHERE --U.COMPANY = 1 AND
          U.ACCOUNT =4000
          AND U.SUB_ACCOUNT =0
          AND U.ACCT_UNIT IN ( Select * from tempdb.#T_Acct)
          AND U.FISCAL_YEAR IN ('+ @PY_Year + ',' + @CY_Year + ')
          GROUP BY U.ACCOUNT,U.FISCAL_YEAR,U.SUB_ACCOUNT,E.[GROUPING],E.ACCOUNT_DESC_ERDETAIL
          '
           

SET @SQL9 =
  'SELECT E.ACCOUNT,E.SUB_ACCOUNT,E.SUB_GRP_DIVISIONAL
,ISNULL(FINAL.FISCAL_YEAR,0)
,CASE WHEN E.ACCOUNT IN (5002,5003) THEN ''TOTAL DIRECT PAYROLL EXPENSES''
      WHEN E.ACCOUNT IN (6070,6255,5005) THEN ''TOTAL DIRECT EXPENSES''
      ELSE E.GROUPING END
,CASE WHEN E.ACCOUNT IN (6235)THEN ''LEGAL FEES - MALPRACTICE''
      WHEN E.ACCOUNT IN (6010)THEN ''EXHIBITIONS''
      --WHEN E.ACCOUNT IN (6360,6370,6380) THEN ''PAYROLL - TAXES''
      ELSE E.ACCOUNT_DESC_ERDETAIL END
,isnull(FINAL.ACTM1,0)
,isnull(FINAL.ACTM2,0)
,isnull(FINAL.ACTM3,0)
,isnull(FINAL.ACT_CYTD,0),isnull(FINAL.ACT_PYTD,0)
,isnull(FINAL.BUDGETM1,0) ,isnull(FINAL.BUDGET_YTD,0)
,(select ltrim(rtrim(ACCT_UNIT)) from RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY WHERE PRT_ORDER =' +cast(@AUcnt as varchar(3))+')
,0
,left(ltrim(rtrim(isnull('''+@title+''',''''))),255)
FROM ER_ACCOUNTS E
LEFT OUTER JOIN
 ('+@SQL1+ ' UNION  ( ' +@SQL3 + ' ) UNION ('+ @SQL2 +') ) AS FINAL
 ON E.ACCOUNT = FINAL.ACCOUNT
AND E.SUB_ACCOUNT = FINAL.SUB_ACCOUNT
where E.ACCOUNT NOT IN (6220,4520,6406,6165,6168,6700,6701)
--AND E.FISCAL_YEAR = FINAL.FISCAL_YEAR'
INSERT INTO RPT_INDIVIDUAL_BURST (
ACCOUNT ,
SUB_ACCOUNT,
SUB_GRP,
FISCAL_YEAR,
HEAD_NAME ,
ACCOUNT_DESC ,
ACTM1,
ACTM2,
ACTM3,
CY_ACT_YTD,
PY_ACT_YTD,
BUDGETM1,
BUDGET_YTD,
REPORTNAME,
ACCOUNT_UNIT,
Title)
EXEC(@SQL9)
--select cast(CURRENT_TIMESTAMP as char(25)) + cast(@AUcnt as char(5))+(@SQL9)
set @AUcnt=@AUcnt+1
end
--SELECT @SQL9
select ACCOUNT ,
SUB_ACCOUNT,
SUB_GRP,
FISCAL_YEAR,
HEAD_NAME ,
ACCOUNT_DESC ,
A.COMPANY,
SUM(ACTM1) AS ACTM1,
SUM(ACTM2)AS ACTM2,
SUM(ACTM3)AS ACTM3,
SUM(CY_ACT_YTD) AS CY_ACT_YTD,
SUM(PY_ACT_YTD) AS PY_ACT_YTD ,
SUM(BUDGETM1) AS BUDGETM1 ,
SUM(BUDGET_YTD) AS BUDGET_YTD,
REPORTNAME as ACCT_UNIT ,
--isnull(A.REPORT_TITLE,Title) as Title,
case when isnull(ltrim(rtrim(A.REPORT_TITLE)),'')='' then B.Title else ltrim(rtrim(A.REPORT_TITLE)) end as Title
, @LastDay AS MONTH_LASTDAY
 ,@Year as Yr
, @Month as Mnth
,PRT_ORDER
,SEQ_NUMBER
 from RPT_INDIVIDUAL_BURST B
 inner join dbo.RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY A
 on A.ACCT_UNIT COLLATE DATABASE_DEFAULT =B.REPORTNAME COLLATE DATABASE_DEFAULT
GROUP BY ACCOUNT ,
SUB_ACCOUNT,
SUB_GRP,
FISCAL_YEAR,
HEAD_NAME ,
ACCOUNT_DESC,
A.COMPANY,
REPORTNAME,
Title,A.REPORT_TITLE
,PRT_ORDER
,SEQ_NUMBER
ORDER BY A.PRT_ORDER
--EXEC (@SQL1)
 
--set @AUcnt=@AUcnt+1
--end
0
Comment
Question by:metalteck
7 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 250 total points
ID: 38857253
you need to declare that variable

declare @Y_YEAR  varchar(4)
SET @Y_YEAR = CAST(@Y_YEAR AS VARCHAR(4))
0
 
LVL 11

Accepted Solution

by:
Simone B earned 250 total points
ID: 38857263
You've declared @CY_YEAR and @PY_YEAR but not @Y_YEAR

You also need to declare @M_Month

There seems to be one "end" too many, on line 224. Although without the data it's hard to tell. But it's throwing a syntax error.

EXEC(@SQL9)
--select cast(CURRENT_TIMESTAMP as char(25)) + cast(@AUcnt as char(5))+(@SQL9)
set @AUcnt=@AUcnt+1

--end

--SELECT @SQL9
0
 

Author Comment

by:metalteck
ID: 38857338
Once I declare those variables it runs, but finds another error at the end of the code:
Lookup Error - SQL Server Database Error: Ambiguous column name 'PRT_ORDER'.
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 11

Expert Comment

by:Simone B
ID: 38857369
Towards the end of your code, the bold columns below need to have the alias specified, either A or B:

 ,@Year as Yr
, @Month as Mnth
,PRT_ORDER
,SEQ_NUMBER
 from RPT_INDIVIDUAL_BURST B
 inner join dbo.RPT_INDIVIDUAL_BURSTING_AU_SPECIALTY A
 on A.ACCT_UNIT COLLATE DATABASE_DEFAULT =B.REPORTNAME COLLATE DATABASE_DEFAULT
GROUP BY ACCOUNT ,
SUB_ACCOUNT,
SUB_GRP,
FISCAL_YEAR,
HEAD_NAME ,
ACCOUNT_DESC,
A.COMPANY,
REPORTNAME,
Title,A.REPORT_TITLE
,PRT_ORDER
,SEQ_NUMBER
ORDER BY A.PRT_ORDER

It's best practice to always use aliases.
0
 
LVL 26

Expert Comment

by:Kurt Reinhardt
ID: 38857377
Does PRT_ORDER exist in multiple tables?  If so, you'll need to preface it with your alias in order to explicitly state the table it should come from when you reference it.  Your use of aliases is very inconsistent in your code, in general, so you should probably clean it up in order to avoid errors like this.
0
 

Author Comment

by:metalteck
ID: 38860285
rhinok, I completely agree with you, but this is code I inherited from my predecessor. I'm new to stored procedures and am learning how to read and write them. You guys answered my question and it runs successfully. I have one last question and I will open another question if need be, but If I hard code the year to be 2012, I get data, but when I do the same for 2013, I don't get a single record. Any suggestions?

Thanks
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now