SQL Server Performance

Scope for table data type

Discussion in 'General DBA Questions' started by Yariv, Dec 22, 2002.

  1. Yariv New Member

    I'm trying to replace my temp tables with table data types, but I'm getting "must delcare" errors for my tables. It doesn't happen all the time.

    Is there any reason why I can't use more than one table data type in a sp?

    Here's an example of code from a sp that gives me such an error:



    create table #periodmsg
    (
    Message_Code numeric(10, 0)
    )

    declare @tempmsg table
    (
    Message_Code numeric(10, 0)
    )

    insert into #periodmsg(Message_Code)
    select TblMessage.Message_Code
    fromTblMessage inner join TblMessageCondition on (TblMessage.Message_Code = TblMessageCondition.Condition_MessageCode)
    whereTblMessageCondition.Condition_TableNum = 5 and
    TblMessageCondition.Condition_DateValue = @period

    insert into @tempmsg(Message_Code)
    select #periodmsg.Message_Code
    from#periodmsg inner join TblMessageCondition on (#periodmsg.Message_Code = TblMessageCondition.Condition_MessageCode)
    whereTblMessageCondition.Condition_TableNum = 1 and
    TblMessageCondition.Condition_NumValue = @usercode

    select count(@tempmsg.Message_Code),
    TblMessage.Message_Title,
    TblMessage.Message_Text
    fromTblMessage inner join @tempmsg on (TblMessage.Message_Code = @tempmsg.Message_Code)
    group by TblMessage.Message_Title,
    TblMessage.Message_Text
    I'm getting "Must declare the variable '@tempmsg'" on the last select.

    Any ideas?
  2. Yariv New Member

    Thanks, but this only produces more errors on the sp variables:



    create procedure bsp_SelectMessageForUser @usercode numeric(10, 0), @period smalldatetime

    as

    set nocount on

    create table #periodmsg
    (
    Message_Code numeric(10, 0)
    )
    go

    declare @tempmsg table
    (
    Message_Code numeric(10, 0)
    )
    go

    insert into #periodmsg(Message_Code)
    select TblMessage.Message_Code
    fromTblMessage inner join TblMessageCondition on (TblMessage.Message_Code = TblMessageCondition.Condition_MessageCode)
    whereTblMessageCondition.Condition_TableNum = 5 and
    TblMessageCondition.Condition_DateValue = @period

    insert into @tempmsg(Message_Code)
    select #periodmsg.Message_Code
    from#periodmsg inner join TblMessageCondition on (#periodmsg.Message_Code = TblMessageCondition.Condition_MessageCode)
    whereTblMessageCondition.Condition_TableNum = 1 and
    TblMessageCondition.Condition_NumValue = @usercode

    set nocount off

    select count(@tempmsg.Message_Code),
    TblMessage.Message_Title,
    TblMessage.Message_Text
    fromTblMessage inner join @tempmsg on (TblMessage.Message_Code = @tempmsg.Message_Code)
    group by TblMessage.Message_Title,
    TblMessage.Message_Text

    And the errors are:


    Server: Msg 137, Level 15, State 2, Line 6
    Must declare the variable '@period'.
    Server: Msg 137, Level 15, State 1, Line 8
    Must declare the variable '@tempmsg'.
    Server: Msg 137, Level 15, State 1, Line 12
    Must declare the variable '@usercode'.
    Server: Msg 137, Level 15, State 1, Line 16
    Must declare the variable '@tempmsg'.


    quote:Originally posted by bradmcgehee

    Insert a GO statement after your create table and your declare statements.


    ------------------
    Brad M. McGehee
    Webmaster
    SQL-Server-Performance.Com
  3. Chappy New Member

    Try a BEGIN/END around your procedure body perhaps?
  4. Yariv New Member

    Thank you for your comment, but this hasn't changed anything.
    Any more ideas?


    quote:Originally posted by Chappy

    Try a BEGIN/END around your procedure body perhaps?
  5. satya Moderator

    I suggest why not you use SP_EXECUTESQL which can execute set of sql statements with parameters.
    The parameters shall be declared in the second parameters, since SQL execute statement as seperate piece of code. In fact it works exactly like temporary stored procedure to wich you pass 1 or more parameters.

    The parameters that will be changed in the statement shall be defined as output just like in a stored procedure declaration.

    Refer to BOL for more information.

    HTH

    Satya SKJ

Share This Page