How can I use a Declare variable inside OPENQUERY-CodePudding

I have following SQL query in SQL Server 2019

DECLARE @CoustomerCode nvarchar(255)

set @CoustomerCode = 1165

select * from tblCustomer CC 
outer apply(SELECT * FROM OPENQUERY ([132.20.28.36], 'SELECT * FROM RepServiceDB.dbo.Info('  @CoustomerCode  ')' ) as SR ) HH
where CC.fldCustomerCode = @CustomerCode

SQL error:

Incorrect syntax near ' '.

CodePudding user response：

You would have to use dynamic SQL, if you must use OPENQUERY, as the query within the call must be a literal:

DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13)   NCHAR(10);

DECLARE @CoustomerCode nvarchar(255);
SET @CoustomerCode = N'1165'; --This is an nvarchar, so you should define it as one.

SET @SQL = N'SELECT *'   @CRLF  
           N'FROM dbo.tblCustomer CC '   @CRLF  
           N'     OUTER APPLY(SELECT * FROM OPENQUERY ([132.20.28.36], ''SELECT * FROM RepServiceDB.dbo.Info('''''  REPLACE(@CoustomerCode,'''','''''''''')  ''''')'' ) as SR ) HH'   @CRLF  
           N'WHERE CC.fldCustomerCode = @CoustomerCode;';

EXEC sys.sp_executesql @SQL, N'@CoustomerCode nvarchar(255)',@CoustomerCode;

And, yes, I really am replacing a single single quote (') with four in the statement. This is because it's a literal string, inside a literal string, inside a literal string, so need double escaping.