Solved

SQL Server Database Error. Must Declare Scaler Variable

Posted on 2013-02-05
7
850 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

751 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