向OpenQuery发送多个值

Question

我在sql server中有以下查询

的例子:

Declare @typeL varchar(max) 
SET @typeL = 'AA,NF' 
OPENQUERY(ORACLEPD, 'SELECT * FROM Ledger where typeLedger in (''+@typeL+'')')

但是它显示了一个错误，因为它不接受动态参数。

Answer 1

这有帮助吗?

Declare @typeL varchar(max) 
SET @typeL = '''AA'',''NF''' 
OPENQUERY(ORACLEPD, 'SELECT * FROM Ledger where typeLedger in ('+@typeL+')')

参考

Answer 2

我有自己的解决方案。(它对我有效)

DECLARE @var VARCHAR(MAX)  
DECLARE @var2 VARCHAR(MAX)  
DECLARE @tb as TABLE(       
VALORES VARCHAR(MAX) )

SET @var = 'AA,NF' 
SET @var2 = ''

INSERT INTO @tb 
SELECT value FROM string_split(@var,',')

WHILE EXISTS(SELECT *  FROM @tb) 
BEGIN
IF (SELECT COUNT(*)FROM @tb)>1  
BEGIN    
SET @var2 = @var2 + (''''+(SELECT TOP 1 * FROM @tb)+'''')+''','''     
DELETE TOP(1) FROM @tb   
END      
ELSE          
BEGIN             
SET @var2 = @var2 + (''''+(SELECT TOP 1 * FROM @tb)+'''')                       
DELETE TOP(1) FROM @tb            END     END

SELECT @var2

openquery看起来像这样:

OPENQUERY(ORACLEPD, 'SELECT * FROM Ledger where typeLedger in (''+@var2+'')')

结果@var2返回:"AA"、"NF">

我分享它，以防有人需要。