Solved

SQL Server Database Error. Must Declare Scaler Variable

Posted on 2013-02-05
7
822 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

747 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

12 Experts available now in Live!

Get 1:1 Help Now