How can I use a Declare variable inside OPENQUERY

Question

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 '+'.

Answer 1

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.