Stored Procedure Table Parameter Issue

  • 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".

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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

  • Nevermind, I see it. Thanks for the help that seemed to work.

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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

  • 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