使用sql server中的Table变量更新查询 [英] Update Query using Table variable in sql server

问题描述

我在程序中有这个表

I have this table in procedure

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

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'

当我使用#tbl_Copy table..means说内存表时，这个工作正常。 />


使用表变量时出现错误

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

推荐答案

您必须运行DECLARE ...并且将@tbl_copy更新为同一个transac的一部分 - 你不能只是突出显示更新并运行它，你必须突出显示两者。



@tbl_copy是一个变量，必须在运行时知道。

当你使用#tbl_Copy时它会起作用，因为这个连接表已经存在

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

这篇关于使用sql server中的Table变量更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！