Must declare the scalar variable /SP Not Found

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 2008 Forums

Transact-SQL (2008)

Must declare the scalar variable /SP Not Found

Author

Topic

beachldy
Starting Member

3 Posts

Posted - 2010-05-25 : 16:06:29

Am always getting this error in SQL 2008 when using variables in Stored Procedures or using T-SQL. What in the world changed in SQL 2008?

See code below. I get the error when it gets to the last part creating the temp table using the @TmtPlanID variable ("must declare the scalar variable). Also, for some reason, this SP is not listed with intellisense when I type T-SQL. It's there though visibly in the SQL items and in the correct database.

------------------------------------
CREATE PROCEDURE [dbo].[SP_CopyTmtPlanSTEPONETEST]
@TmtPlanID int,
@NewTmtPlanDt datetime
AS

-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

Declare @NewTmtPlanID Int
Declare @OldGID int
Declare @NewGID Int

/*********************************************************************
Insert TmtPlanHeader
**********************************************************************/
INSERT INTO RBHS_TmtPlanHDR_4a (TmtPlanDate,StudentCtr,TmtPlanDesc,PresentingProblem,
AppropOfSvcs,NeedforContinuedSvc,RecommForContCare)
SELECT @NewTmtPlanDt,StudentCtr,TmtPlanDesc,PresentingProblem, AppropOfSvcs,
NeedforContinuedSvc ,RecommForContCare FROM vw_TmtPlanToCopy
where TmtPlanID = @TmtPlanID
Group By TmtPlanDate,StudentCtr,TmtPlanDesc,PresentingProblem,
AppropOfSvcs,NeedforContinuedSvc,RecommForContCare

/*********************************************************************
Get New Tmt Plan ID
**********************************************************************/
Set @NewTmtPlanID = SCOPE_IDENTITY()

/*********************************************************************
Insert Goals and get new id
**********************************************************************/
Insert into BHServices.dbo.RBHS_TPGoals_4aa (TmtPlanID, Goal)
Select @NewTmtPlanID, Goal FROM vw_TmtPlanToCopy
where TmtPlanID = @TmtPlanID
Group By Goal

Set @NewGID = SCOPE_IDENTITY()
--select @NewGID

/*********************************************************************
Create Temp tables and insert objectives
**********************************************************************/
IF OBJECT_ID('tempdb..#tempGoals') IS NOT NULL
DROP TABLE [dbo].[#tempGOALS]
GO
--SELECT * FROM sys.objects WHERE object_id = OBJECT_ID('[dbo].[#TempGOALS]')
Create Table #tempGOALS
(OLDTMTPLANID int,
NEWTMTPLANID int,
OLDGID int,
NEWgid int,
Goal nvarchar (255))

INSERT INTO #tempGOALS (OLDTMTPLANID, NEWTMTPLANID, OLDGID, NEWgid, Goal )
SELECT 2 AS OLDTMTPLANID, 19 AS NEWTMTPLANID, RBHS_TPGoals_4aa.GID AS OLDGID, (null),
RBHS_TPGoals_4aa.Goal
FROM RBHS_TmtPlanHDR_4a
LEFT JOIN RBHS_TPGoals_4aa ON
RBHS_TmtPlanHDR_4a.TmtPlanID = RBHS_TPGoals_4aa.TmtPlanID
WHERE RBHS_TmtPlanHDR_4a.TmtPlanID= @TmtPlanID

Select * from #tempGOALS

GO

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-05-25 : 16:24:08

That is nothing new and has nothing to do with 2008, a GO is the end of a batch and after that GO no declarations from before are known...

No, you're never too old to Yak'n'Roll if you're too young to die.

beachldy
Starting Member

3 Posts

Posted - 2010-05-25 : 19:34:06

Thanks. I discovered that after the posting.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources