Passing Variable Into OpenQUERY SQL Server 2016

207 views Asked by At

EDIT: SQL Server Version I'm trying to pass this variable into my open query using this guide from Microsoft: Link

I'm running into this error message "Statement(s) could not be prepared." Which I believe means something is wrong with the OpenQuery. I'm just not sure what is wrong. Here's the code:

DECLARE @ticketid INT, @QLFD VARCHAR(8000)
SELECT @ticketid = '296272348'
SELECT @QLFD = 'SELECT
                    *
                FROM 
                    OPENQUERY(
                    [Server_name],''
                        SELECT
                            ticket_id
                            , QLFD_SPD_AMT
                        FROM [database].[dbo].[table]
                        WHERE ticket_id = @ticketid
                    '')'
EXEC (@QLFD)

Could you help me identify the error? I prefer to do it passing the whole query as one. Thanks!
Edit:
After looking at suggestions made by @Larnu. I have adjusted my code to:

DECLARE @ticketid INT--, @QLFD NVARCHAR(Max)
SELECT @ticketid = '296272348'
DECLARE @QLFD NVARCHAR(Max) = 'SELECT
                    *
                FROM 
                    OPENQUERY(
                    [Server_name],''
                        SELECT
                            ticket_id
                            , QLFD_SPD_AMT
                        FROM [database].[dbo].[table]
                        WHERE ticket_id = QUOTENAME(@ticketid, '''''''')
                    '')';
EXEC (@QLFD);
1

There are 1 answers

0
Thom A On BEST ANSWER

As I mentioned, you can't parametrise a query with OPENQUERY you have safely inject the values.

Normally that would be with QUOTENAME or REPLACE, but you don't actually need to do that here, due to the value being a numerical data type, so you can just concatenate it in:

DECLARE @ticketid int = 296272348; --Don't wrap numerical datatypes with quotes.

DECLARE @SQL nvarchar(MAX),
        @OpenQuery nvarchar(4000);

SET @OpenQuery = CONCAT(N'SELECT QLFD_SPD_AMT
FROM [database].[dbo].[table]
WHERE ticket_id = ',@ticketid,N';'); --As it's an int we dont need to quote

SET @SQL = CONCAT(N'SELECT @ticketid AS ticket_id, QLFD_SPD_AMT
FROM OPENQUERY([servername],N''',REPLACE(@OpenQuery,'''',''''''),N''');';

EXEC sys.sp_executesql @SQL, N'@ticketid int', @ticketid;