November 2, 2011 at 11:43 am
I am getting a "Must declare the scalar variable @jobtbl". I don't see what is wrong with the syntax. Any help would be appreciated.
Table Type Code
CREATE TYPE [dbo].[JobTableType] AS TABLE(
[JobN] [float] NOT NULL,
PRIMARY KEY CLUSTERED
(
[JobN] ASC
)WITH (IGNORE_DUP_KEY = OFF)
)
GO
Stored Procedure Code
Create PROCEDURE [dbo].[proc_AdditionalInfotbl] (@jobtbl JobTableType READONLY) AS
Begin
Update additionalinfo
Set RunningUnwind = Case unwindfrnt when 5 then 1 when 6 then 2
when 7 then 3 when 8 then 4 else unwindfrnt end
from AdditionalInfo inner join @jobtbl on AdditionalInfo.JobN = @JobTbl.jobN
end
exact error message:
Msg 137, Level 16, State 1, Procedure proc_AdditionalInfotbl, Line 6
Must declare the scalar variable "@jobtbl".
November 2, 2011 at 11:49 am
Create PROCEDURE [dbo].[proc_AdditionalInfotbl] (@jobtbl JobTableType READONLY) AS
Begin
Update additionalinfo
Set RunningUnwind = Case unwindfrnt when 5 then 1 when 6 then 2
when 7 then 3 when 8 then 4 else unwindfrnt end
from AdditionalInfo inner join @jobtbl j on AdditionalInfo.JobN = j.jobN
end
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 2, 2011 at 11:54 am
Gail,
I'm not sure if I'm missing something but I don't see a difference of what you posted as compared to what I did.
Jason
November 2, 2011 at 11:55 am
You can't use table variable names in column-qualification. Have to alias them for that.
Exact problem is "@JobTbl.jobN".
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 2, 2011 at 11:56 am
Nevermind, I see it. Thanks for the help that seemed to work.
November 2, 2011 at 12:23 pm
jjmanthei05 (11/2/2011)
Gail,I'm not sure if I'm missing something but I don't see a difference of what you posted as compared to what I did.
Your from clause:
from AdditionalInfo inner join @jobtbl on AdditionalInfo.JobN = @JobTbl.jobN
My from clause
from AdditionalInfo inner join @jobtbl j on AdditionalInfo.JobN = j.jobN
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
November 3, 2011 at 12:56 am
GSquared (11/2/2011)
You can't use table variable names in column-qualification.
Actually you can. Just put square brackets around the variable name, although I prefer the alias.
Create PROCEDURE [dbo].[proc_AdditionalInfotbl] (@jobtbl JobTableType READONLY) AS
Begin
Update additionalinfo
Set RunningUnwind = Case unwindfrnt when 5 then 1 when 6 then 2
when 7 then 3 when 8 then 4 else unwindfrnt end
from AdditionalInfo inner join @jobtbl on AdditionalInfo.JobN = [@jobtbl].jobN
end
November 3, 2011 at 3:06 am
create PROCEDURE [dbo].[proc_AdditionalInfotbl] (@jobtbl JobTableType READONLY) AS
Begin
Update additionalinfo
Set RunningUnwind = Case unwindfrnt when 5 then 1 when 6 then 2
when 7 then 3 when 8 then 4 else unwindfrnt end
from AdditionalInfo inner join @jobtbl j on AdditionalInfo.JobN = j.jobN
end
I think you have not defined alias correctly for @jobtbl i.e J
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply