Click here to Skip to main content
15,886,362 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have this table in procedure

SQL
DECLARE @tbl_copy TABLE 
(
	BasicObsEntered NVARCHAR(20),
	BasicObsEnteredDate NVARCHAR(100),
	[STATUS] nvarchar(10),
	CategoryOne nvarchar(30),
	NAME nvarchar(125),
	ItemName nvarchar(125),
	[Value] nvarchar(60),
	UnitOfMeasure nvarchar(15),
	AbnormalityCode nvarchar(10),
	ReferenceUpperLimit nvarchar(10),
	ReferenceLowerLimit nvarchar(10),
	ResultGUID NVARCHAR(32)
)

I inserted data in this table successfully and wrote following update Query

SQL
Update @tbl_copy SET BasicobsEntered = b.BasicobsEntered , BasicObsEnteredDate =b.BasicObsEnteredDate from @tbl_copy INNER JOIN @tbl_cpy1 b on @tbl_copy.Name = @tbl_cpy1.Name and @tbl_copy.ItemName = @tbl_cpy1.ItemName and b.[Status]='F' and @tbl_copy.Status='P'


This works fine when i am using #tbl_Copy table..means to say memory table.

Error comes when using table variable as
Msg 137, Level 16, State 1, Procedure ResultList_Optimization, Line 172<br />
Must declare the scalar variable "@tbl_copy".
Posted
Updated 29-Jan-13 21:48pm
v2

1 solution

You must run the DECLARE ... and the Update @tbl_copy as part of the same transaction - you can't just highlight the update and run that, you have to highlight both.

@tbl_copy is a variable and must be known at run-time.
It works when you use #tbl_Copy because the table already exists on this connection
 
Share this answer
 
Comments
deepakdynamite 30-Jan-13 8:38am    
Thanks for your Help. For your information, I am using this under one procedure so i guess execution of whole procedure lies under one transaction. And all variable declarations are done on top of Procedure and after that data is inserted into it and then updated.

Any other that can help ?
CHill60 30-Jan-13 9:55am    
Can you confirm that this query is on line 172 of your proc and the definition of @tbl_copy1
deepakdynamite 5-Feb-13 3:31am    
yes Declaration is made first and then update query is written

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900