inserting a variable into openquery

I’m trying to insert a variable into the openquery expression, but unfortunately I get an error, does anyone know where I got this error?

DECLARE @NIPPar varchar(max);

SET @NIPPar = '434435435';



DECLARE @NIPParInt int;

SET @NIPParInt = 434435435;


SELECT NIP FROM  
 (SELECT 
 NIP
  FROM OPENQUERY(LEA_PROD, 'SELECT * FROM CEBTE.TEST WHERE NIP = ''@NIPParInt''') e ) I
  

Arithmetic overflow error when converting expression data to int data type.

Answer

OPENQUERY requires a literal string, you can’t put variables into it. As such you’ll need to create a dynamic statement and inject the value into the query safely:

DECLARE @NIPPar varchar(max); --Does this really need to be up to 2 Billion characters?
                              --OPENQUERY will truncate at 8,000 bytes, so you could easily suffer truncation issues if so. 
SET @NIPPar = '434435435';

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

SET @SQL = N'SELECT NIP' + @CRLF +
           N'FROM OPENQUERY (LEA_PROD, ''SELECT NIP FROM CEBTE.TEST WHERE NIP = ''''' + REPLACE(@NIPPar, '''','''''''''') + ''''') OQ;';

EXEC sys.sp_executesql @SQL;

Though, I must admit, I don’t see much point in this as it’s simply going to return the input parameter if the row(s) exist.