Sql选择使用动态SQL的位置 [英] Sql select where in using dynamic SQL

问题描述

您好，



我正在使用动态sql而我正在尝试从另一个表执行'where in'，但是我遇到语法问题。



我尝试了什么：

  DECLARE   @ accountprofit   nvarchar （max） 
  DECLARE   @ accountprofit2   nvarchar  （max）
  DECLARE   @ accountprofile   nvarchar （max）
  DECLARE   @ accountprofile2   nvarchar （max）
 
 
  set   @ accountprofit  = ' 从G.DBO.Clients中选择cust_no' 
  EXEC （ @ accountprofit ）
 
 
  SET   @ accountprofile2  = '  SELECT ACC，
 CUST 
来自ACCOUNTTABLE AP 
 WHERE ap.cust in（' +  @ accountprofit  + ' ）' 
 
  exec   @ accountprofile2  
 
  SET   @ accountprofile  = '  SELECT * into ## accountprofile FROM OPENQUERY（[MYSERVER]，' + ' '' ' +  @ accountprofile2  + ' ''' + ' ）' 
 
  EXEC （ @ accountprofile  ）
 
 从 <＃accountprofile 

解决方案

没有你的架构（我懒于使用var表进行逆向工程），这有点难以确定。



当放入一个变量时，你的sql看起来要编译成这个

  SELECT  *  into  ## accountprofile  FROM   OPENQUERY （[MYSERVER]，'  SELECT ACC ，CUST来自ACCOUNTTABLE AP，其中ap.cust in（选择cust_no来自G.DBO.Clients）'）

我的最好的猜测是你的[MYSERVER]旁边的SELECT语句已经作为字符串放在那里，而是需要看起来像

  SELECT  *  into  ## accountprofile  FROM  < span class =code-keyword> OPENQUERY （[MYSERVER]，（ SELECT  ACC，CUST  FROM  ACCOUNTTABLE AP  WHERE  ap.cust  in （ select  cust_no 来自 G.DBO.Clients）））

要实现这一目标，您需要在@accountprofile变量。

  SET   @ accountprofile  = '  SELECT * into ## accountprofile FROM OPENQUERY（[MYSERVER]，（' +  @ accountprofile2  + ' ））' 

如果这不能解决您的问题，那么您是否愿意提供一些架构来运行此查询以及实际错误如果我有空的话，我很乐意看看。

Hi there,

I am using dynamic sql and I am trying to perform a 'where in' from another table, however I am having syntax issues.

What I have tried:

DECLARE @accountprofit nvarchar(max)
DECLARE @accountprofit2 nvarchar(max)
DECLARE @accountprofile nvarchar(max)
DECLARE @accountprofile2 nvarchar(max)


set @accountprofit = 'select cust_no from G.DBO.Clients'
EXEC(@accountprofit)


SET @accountprofile2 = 'SELECT ACC,
CUST
FROM ACCOUNTTABLE   AP
WHERE ap.cust in ('  + @accountprofit +  ') '

exec @accountprofile2

SET @accountprofile = 'SELECT * into ##accountprofile FROM OPENQUERY([MYSERVER],' + '''' + @accountprofile2 + '''' + ')'

EXEC(@accountprofile)

select * from ##accountprofile

解决方案

Without your schema (i'm to lazy to reverse engineer using var tables) this is going to be a bit difficult to be sure.

When placed into one variable, your sql looks to compile down to this

SELECT * into ##accountprofile FROM OPENQUERY([MYSERVER],'SELECT ACC,CUST FROM ACCOUNTTABLE AP WHERE ap.cust in (select cust_no from G.DBO.Clients)')

My best guess is that your SELECT statement beside [MYSERVER] has been placed in there as a string, instead it needs to look like

SELECT * into ##accountprofile FROM OPENQUERY([MYSERVER],(SELECT ACC,CUST FROM ACCOUNTTABLE AP WHERE ap.cust in (select cust_no from G.DBO.Clients)))

To achieve this you would do the following in your @accountprofile variable.

SET @accountprofile = 'SELECT * into ##accountprofile FROM OPENQUERY([MYSERVER], (' + @accountprofile2 + '))'

Should this not solve your problem then if you would like to provide some schema to use to run this query against along with the actual error message i'll be happy to take a look when i have time.

这篇关于Sql选择使用动态SQL的位置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！