SQL Server Performance Forum – Threads Archive

Scope for table data type

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?

---

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’.

---

Try a BEGIN/END around your procedure body perhaps?

---

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

---

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

---

]]>