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);
As I mentioned, you can't parametrise a query with
OPENQUERY
you have safely inject the values.Normally that would be with
QUOTENAME
orREPLACE
, 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: