在Openquery中使用Temp表变量 [英] Using Temp table variable in Openquery
问题描述
大家好,
我正在创建临时表变量并使用打开的查询中的记录填充它。
然后我尝试在另一个openquery中的连接中使用此临时表变量。
< span style ="color:#ff0000; font-size:small">
但它发出的错误是"无效 表名'...有人可以帮忙吗?
But its throwing an error saying 'Invalid table name'...Can anyone help please?
DECLARE @MOREV
表
( id1
int ,
(id1 int,
MOR1 int ,
MOR1 int,
MOR2
int ,
MOR2 int,
id2 int ,
id2 int,
ROWNUMBER int )
ROWNUMBER int)
< span style ="background:white; font-size:10pt"> DECLARE @SQL
varchar ( MAX )
DECLARE @SQL varchar(MAX)
DECLARE @ SQL1
varchar ( MAX )
DECLARE @SQL1 varchar(MAX)
SET @SQL
= 'select * from openquery(BRISPRD,''
SET @SQL = 'select * from openquery(BRISPRD,''
SELECT ev.id
SELECT ev.id
<跨度>&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP ;
,re.mor1 mor1
,re.mor1 mor1
,re2.mor2 mor2
,re2.mor2 mor2
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
,ev.id2
,ev.id2
&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP; &NBSP;&NBSP;&NBSP;&NBSP;&NBSP;&NBSP;
,ROW_NUMBER()OVER(由ev.EV_ID desc评估ev.EPI_ID订单,ev.EVENT_START_DATETIME)ROWNUMBER
,ROW_NUMBER() OVER(PARTITION BY ev.EPI_ID ORDER BY ev.EV_ID desc, ev.EVENT_START_DATETIME) ROWNUMBER
FROM
EVENTS ev
xx上的LEFT JOIN xxx .id = ev.id
LEFT JOIN yyy on ev.id1 = yyy .id1
''''
将
插入 @MOREV
Insert Into @MOREV
EXEC ( @ SQL )
EXEC(@SQL)
SET @ SQL1
= 'select * from openquery(BRISPRD,''
SET @SQL1 = 'select * from openquery(BRISPRD,''
SELECT aaa,bbb,ccc
SELECT aaa,bbb,ccc
FROM
review rrr
LEFT JOIN'+ @ MOREV +'mo on mo.id = rrr.id
'')
EXEC(@SQL)'
推荐答案
大家好,
我正在创建一个临时表变量,并用打开的查询中的记录填充它。
然后我试图在另一个openquery中的连接中使用这个临时表变量。
但是它会抛出一个错误,上面写着'无效的表名'...有人可以帮忙吗?
But its throwing an error saying 'Invalid table name'...Can anyone help please?
美好的一天
Nigss ,
>>你说的是"临时表变量"我只能猜测你是指类型表的变量,因为临时表完全是其他东西。临时表
是通过使用标记#或##以及使用标记@
>> You speak about "temp table variable" which I can only guess that you mean a variable of type table since temp table is totally something else. A temp table is created by using the mark # or ## and a variable declared by using the mark @
>> 声明的变量如果您想在动态查询中使用变量,那么您应该使用 强> sp_executesql
而不是EXEC / EXECUTE,它只输入字符串而不是变量。
>> if you want to use variable in your dynamic query then you should use sp_executesql and not EXEC/EXECUTE which only get input of a string and not variables.
>>您的方法毫无意义,因为@ SQL1是字符串类型而@MOREV是表格类型变量。如何计算"字符串+表格"。
>> Your approach make no sense since @SQL1 is a string type and @MOREV is a table type variable. How can you calculate the "string + table".
这篇关于在Openquery中使用Temp表变量的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!