Passing Variable Into OpenQUERY SQL Server 2016

Question

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);

Does this answer your question? How to pass date variable in open query? — SMor
– SMor, Commented Sep 27, 2020 at 19:08
You can't parametrise a query in OPENQUERY; you would need to safely inject the value. — Thom A
– Thom A ♦, Commented Sep 27, 2020 at 19:45
@Larnu, could you show me some documentation that will show me how to safely inject the variable? — GB23
– GB23, Commented Sep 27, 2020 at 19:50
There isn't really "documentation" on it. Just ensure you use QUOTENAME appropriately, or (for longer strings) ensure you use REPLACE to quote the value properly. — Thom A
– Thom A ♦, Commented Sep 27, 2020 at 19:59
@Larnu, So I adjusted the bottom line of the query to WHERE ticket_id = QUOTENAME(@ticketid, '''''''') Adding triple quote in the query since the openquery starts with a double. Still getting an error. Thanks anyways! — GB23
– GB23, Commented Sep 27, 2020 at 20:08

Thom A · Accepted Answer · 2020-09-27 20:26:03Z

1

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;

edited Sep 27, 2020 at 20:26

answered Sep 27, 2020 at 20:17

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Passing Variable Into OpenQUERY SQL Server 2016

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related