[sql] OPENQUERY에 매개 변수 포함

Program / 글쓴이

다음과 같은 SQL openquery 내에서 매개 변수를 어떻게 사용할 수 있습니까? 

SELECT * FROM OPENQUERY([NameOfLinkedSERVER], 'SELECT * FROM TABLENAME
where field1=@someParameter') T1 INNER JOIN MYSQLSERVER.DATABASE.DBO.TABLENAME
T2 ON T1.PK = T2.PK

답변

로부터 OPENQUERY의 문서는 그 상태 :

OPENQUERY는 인수에 대한 변수를 허용하지 않습니다.

해결 방법 은이 문서 를 참조하십시오 .

최신 정보:

제안 된대로 아래 기사의 권장 사항을 포함합니다.

기본 값 통과

기본 Transact-SQL 문을 알고 있지만 하나 이상의 특정 값을 전달해야하는 경우 다음 샘플과 유사한 코드를 사용합니다. 

DECLARE @TSQL varchar(8000), @VAR char(2)
SELECT  @VAR = 'CA'
SELECT  @TSQL = 'SELECT * FROM OPENQUERY(MyLinkedServer,''SELECT * FROM pubs.dbo.authors WHERE state = ''''' + @VAR + ''''''')'
EXEC (@TSQL)

전체 쿼리 전달

전체 Transact-SQL 쿼리 또는 연결된 서버의 이름 (또는 둘 다)을 전달해야하는 경우 다음 샘플과 유사한 코드를 사용합니다.

DECLARE @OPENQUERY nvarchar(4000), @TSQL nvarchar(4000), @LinkedServer nvarchar(4000)
SET @LinkedServer = 'MyLinkedServer'
SET @OPENQUERY = 'SELECT * FROM OPENQUERY('+ @LinkedServer + ','''
SET @TSQL = 'SELECT au_lname, au_id FROM pubs..authors'')'
EXEC (@OPENQUERY+@TSQL)

Sp_executesql 저장 프로 시저 사용

다중 계층 따옴표를 피하려면 다음 샘플과 유사한 코드를 사용하십시오.

DECLARE @VAR char(2)
SELECT  @VAR = 'CA'
EXEC MyLinkedServer.master.dbo.sp_executesql
N'SELECT * FROM pubs.dbo.authors WHERE state = @state',
N'@state char(2)',
@VAR

답변

일단 빌드하면 OPENQUERY로 문자열을 실행할 수 있습니다. 이 길로 가면 보안에 대해 생각하고 사용자가 입력 한 텍스트를 SQL에 연결하지 않도록주의하십시오!

DECLARE @Sql VARCHAR(8000)
SET @Sql = 'SELECT * FROM Tbl WHERE Field1 < ''someVal'' AND Field2 IN '+ @valueList
SET @Sql = 'SELECT * FROM OPENQUERY(SVRNAME, ''' + REPLACE(@Sql, '''', '''''') + ''')'
EXEC(@Sql)

답변

로부터 MSDN 페이지 :

OPENQUERY는 인수에 대한 변수를 허용하지 않습니다.

기본적으로 이것은 동적 쿼리를 실행할 수 없음을 의미합니다. 샘플이 시도하는 것을 얻으려면 다음을 시도하십시오.

SELECT * FROM
   OPENQUERY([NameOfLinkedSERVER], 'SELECT * FROM TABLENAME') T1
   INNER JOIN
   MYSQLSERVER.DATABASE.DBO.TABLENAME T2 ON T1.PK = T2.PK
where
   T1.field1 = @someParameter

TABLENAME 테이블에 많은 양의 데이터가 포함 된 경우 분명히 네트워크를 통해 전송되며 성능이 저하 될 수 있습니다. 반면에 소량의 데이터의 경우 이는 잘 작동하며 exec접근 방식에 필요할 수있는 동적 SQL 생성 오버 헤드 (SQL 주입, 이스케이프 따옴표)를 방지 합니다.

답변

실제로이 작업을 수행하는 방법을 찾았습니다.

DECLARE @username varchar(50)
SET @username = 'username'
DECLARE @Output as numeric(18,4)
DECLARE @OpenSelect As nvarchar(500)
SET @OpenSelect = '(SELECT @Output = CAST((CAST(pwdLastSet As bigint) / 864000000000) As numeric(18,4)) FROM OpenQuery (ADSI,''SELECT pwdLastSet
                                FROM  ''''LDAP://domain.net.intra/DC=domain,DC=net,DC=intra''''
                                WHERE objectClass =  ''''User'''' AND sAMAccountName = ''''' + @username + '''''
                          '') AS tblADSI)'
EXEC sp_executesql @OpenSelect, N'@Output numeric(18,4) out', @Output out
SELECT @Output As Outputs

이렇게하면 @Output 변수에 OpenQuery 실행 결과가 할당됩니다.

MSSQL 2012에서 저장 프로 시저를 테스트했지만 MSSQL 2008+에서 작동해야합니다.

Microsoft는 sp_executesql (Transact-SQL) : 적용 대상 : SQL Server (SQL Server 2008 ~ 현재 버전), Windows Azure SQL Database (최초 릴리스 ~ 현재 릴리스)에 적용됩니다. ( http://msdn.microsoft.com/en-us/library/ms188001.aspx )

답변

DECLARE @guid varchar(36);  select @guid= convert(varchar(36), NEWID() );
/*
    The one caveat to this technique is that ##ContextSpecificGlobal__Temp should ALWAYS have the exact same columns.
    So make up your global temp table name in the sproc you're using it in and only there!
    In this example I wanted to pass in the name of a global temporary table dynamically.  I have 1 procedure dropping
    off temporary data in whatever @TableSrc is and another procedure picking it up but we are dynamically passing
    in the name of our pickup table as a parameter for OPENQUERY.
*/
IF ( OBJECT_ID('tempdb..##ContextSpecificGlobal__Temp' , 'U') IS NULL )
    EXEC ('SELECT * INTO ##ContextSpecificGlobal__Temp FROM OPENQUERY(loopback, ''Select *,''''' +  @guid +''''' as tempid FROM ' + @TableSrc + ''')')
ELSE
    EXEC ('INSERT ##ContextSpecificGlobal__Temp SELECT * FROM OPENQUERY(loopback, ''Select *,''''' +  @guid +''''' as tempid FROM ' + @TableSrc + ''')')

--If this proc is run frequently we could run into race conditions, that's why we are adding a guid and only deleting
--the data we added to ##ContextSpecificGlobal__Temp
SELECT * INTO #TableSrc FROM ##ContextSpecificGlobal__Temp WHERE tempid = @guid

BEGIN TRAN t1
    IF ( OBJECT_ID('tempdb..##ContextSpecificGlobal__Temp' , 'U') IS NOT NULL )
    BEGIN
        -- Here we wipe out our left overs if there if everyones done eating the data
        IF (SELECT COUNT(*) FROM ##ContextSpecificGlobal__Temp) = 0
            DROP TABLE ##ContextSpecificGlobal__Temp
    END
COMMIT TRAN t1

-- YEAH! Now I can use the data from my openquery without wrapping the whole !$#@$@ thing in a string.

답변

SELECT field1 FROM OPENQUERY
                   ([NameOfLinkedSERVER],
                   'SELECT field1 FROM TABLENAME')
                           WHERE field1=@someParameter T1
                                 INNER JOIN MYSQLSERVER.DATABASE.DBO.TABLENAME
                                 T2 ON T1.PK = T2.PK

답변

동적 SQL을 OpenQuery와 결합합니다. (이것은 Teradata 서버로 이동합니다)

DECLARE
    @dayOfWk    TINYINT = DATEPART(DW, GETDATE()),
    @qSQL       NVARCHAR(MAX) = '';

SET @qSQL = '
SELECT
    *
FROM
    OPENQUERY(TERASERVER,''
        SELECT DISTINCT
            CASE
                WHEN ' + CAST(@dayOfWk AS NCHAR(1)) + ' = 2
                THEN ''''Monday''''
                ELSE ''''Not Monday''''
            END
        '');';

EXEC sp_executesql @qSQL;