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.
| Author |
Topic |
|
metser
Starting Member
27 Posts |
 Posted - 2007-12-10 : 11:50:31
|
| Hello, I have the following SP, which gives out a "Error 137: Must declare variable @tmp_return_tbl" error.This is the important part of the code:...-- DECLARE TABLE VARIABLEDECLARE @tmp_return_tbl TABLE (tID int, Text_Title nvarchar(30), Text_Body nvarchar(100)) -- fill out table variable USING A SELECT FROM ANOTHER TABLE VARIABLE-- NO PROBLEM HERE INSERT INTO @tmp_return_tbl SELECT TOP 1 * FROM @tmp_tbl ORDER BY NEWID()-- TRYING TO UPDATE A TABLE UPDATE xTable SET xTable.fieldY = xTable.fieldY + 1 WHERE xTable.tID = @tmp_return_tbl.tID --THIS PRODUCES THE ERROR...I know I cannot use a table variable in a JOIN without using an Alias, or use it directly in dynamic SQL (different scope) - but is this the problem here? What am I doing wrong?Your help is much appreciated. |
|
|
jhocutt
Constraint Violating Yak Guru
385 Posts |
Posted - 2007-12-10 : 12:41:17
|
| WHERE xTable.tID = @tmp_return_tbl.tIDshould be WHERE xTable.tID in(select tID from @tmp_return_tbl )"God does not play dice" -- Albert Einstein"Not only does God play dice, but he sometimes throws them where they cannot be seen." -- Stephen Hawking |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-12-10 : 12:47:15
|
quote:
Originally posted by jhocutt
WHERE xTable.tID = @tmp_return_tbl.tIDshould be WHERE xTable.tID in(select tID from @tmp_return_tbl )
Joins are preferred over subqueries. UPDATE xSET fieldY = x.fieldY + 1FROM xTable xINNER JOIN @tmp_return_tbl tON x.tID = t.tIDTara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
metser
Starting Member
27 Posts |
Posted - 2007-12-10 : 14:58:04
|
| Thank you both for your answers! |
|
|
|
|
|
|
Error 137: Must declare variable ...