Must declare the scalar variable

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

SQL Server 2005 Forums

Transact-SQL (2005)

Must declare the scalar variable

Author

Topic

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2009-05-19 : 05:31:14

Hi Guys.

I hope im in the right section here.

I'm using SQL 2005.

When i execute a SP this is the error i get.

/////////MY ERROR/////////
Msg 137, Level 15, State 2, Line 17
Must declare the scalar variable "@ProjectCode".
(1 row(s) affected)
/////////MY ERROR/////////

My Stored procedure is as follows.

/////////MY SP/////////
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROC [dbo].[GetBudgetReportPerCompany]
(
@SapDB varchar(50),
@ProjectCode varchar(50)
)
AS
BEGIN
SET NOCOUNT ON
DECLARE @SQL varchar(600)
SET @SQL =
'
SELECT
b.ProjectCode,
b.BudgetMonth,
b.BudgetYear,
b.ExpenseBudget,
b.ExpenseForcast,
b.IncomeBudget,
b.IncomeForcast, ISNULL(a.ActualIncome, 0) AS ActualIncome,
ISNULL(c.ActualExpense, 0) AS ActualExpense
FROM dbo.BudgetColsView AS b
LEFT OUTER JOIN
dbo.ActualIncome_'+@SapDB+' AS a ON b.BudgetMonth = a.BudgetMonth AND b.ProjectCode = a.Project
LEFT OUTER JOIN
dbo.ActualExpense_'+@SapDB+' AS c ON b.BudgetMonth = c.BudgetMonth AND b.ProjectCode = c.Project

where b.ProjectCode = @ProjectCode

GROUP BY
b.BudgetYear,
b.BudgetMonth,
b.ProjectCode,
b.ExpenseBudget,
a.ActualIncome,
c.ActualExpense,
b.ExpenseForcast,
b.IncomeBudget,
b.IncomeForcast,
c.ActualExpense
'
EXEC(@SQL)
END
/////////MY SP/////////

Can someone please help, where do i declare it?

Thanks in advance.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-19 : 05:33:36

~~where b.ProjectCode = @ProjectCode~~

where b.ProjectCode = ' + QUOTENAME(@ProjectCode, '''') + '

Also make sure the 600 characters are enough in your declaration for @sql variable.

E 12°55'05.63"
N 56°04'39.26"

mvanwyk
Yak Posting Veteran

99 Posts

Posted - 2009-05-19 : 05:42:11

quote:
Originally posted by Peso

~~where b.ProjectCode = @ProjectCode~~

where b.ProjectCode = ' + QUOTENAME(@ProjectCode, '''') + '

Also make sure the 600 characters are enough in your declaration for @sql variable.

E 12°55'05.63"
N 56°04'39.26"

Thank you Peso.

That worked, will go and read up on QUOTENAME

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources