Output a variable from stored procedure that uses dynamic SQL and OPENQUERY

Question

I found a guide to output the results of a dynamic SQL stored procedure into a variable, but the tricky part for my issue is that I'm using OPENQUERY, which makes using dynamic SQL a bit more challenging. The linked server has a database I'm trying to query called Global Shop Solutions and the provider is Microsoft OLE DB Provider for ODBC Drivers.

The attempt below gives and error when I try to execute it:

Incorrect syntax near '@LinkedServer'

CREATE PROCEDURE [dbo].[DateLastUsage] 
    (@Part nvarchar(50),
     @LinkedServer nvarchar(50),
     @DateLastUsage datetime2 OUTPUT)
AS
BEGIN
    DECLARE @SQL nvarchar(4000)

    SET @SQL = N'SELECT TOP 1 @DateLastUsage = DateLastUsage FROM OPENQUERY(@LinkedServer, ''SELECT MAX(date_history) DateLastUsage FROM v_inventory_hist WHERE part = ''@Part'' '' )'

    EXEC sp_executesql @SQL, N'@LinkedServer nvarchar(50), @Part nvarchar(50), @DateLastUsage datetime2 output', @LinkedServer = @LinkedServer, @Part = @Part, @DateLastUsage = @DateLastUsage output
END

DECLARE @DateLastUsage datetime2
EXEC dbo.GSSDateLastUsage 'PartA', 'GSS', @DateLastUsage OUTPUT

SELECT @DateLastUsage

Answer 1

As documented:

OPENQUERY does not accept variables for its arguments.

So you'd need to use dynamic SQL for that also. Eg:

    set @SQL = N'
select top 1 @DateLastUsage = DateLastUsage
from openquery(' + QUOTENAME(@LinkedServer) + ', ''select max(date_history) DateLastUsage from v_inventory_hist where part = ''' + QUOTENAME(@Part, '''') + ''' '' )
';

Note the way @Part is embedded directly in the query, otherwise it also doesn't work/

---

But to be honest, it's not clear why you are using OPENQUERY anyway. You can query the linked server directly from linkedserver...v_inventory_hist. Now you can pass the parameters properly.

CREATE OR ALTER PROCEDURE dbo.DateLastUsage
    @Part nvarchar(50)
  , @LinkedServer sysname
  , @DateLastUsage datetime2 output
AS
    
DECLARE @SQL nvarchar(max) = N'
SELECT @DateLastUsage = MAX(date_history) 
FROM ' + QUOTENAME(@LinkedServer) + '...v_inventory_hist
WHERE part = @Part;
';

EXEC @SQL,
   N'@Part nvarchar(50),
     @DateLastUsage datetime2 output',

     @Part = @Part,
     @DateLastUsage = @DateLastUsage output;

Or, if the linked server is also SQL Server, you can construct a @proc variable which points to sp_executesql on the remote server.

CREATE OR ALTER PROCEDURE dbo.DateLastUsage
    @Part nvarchar(50)
  , @LinkedServer sysname
  , @DateLastUsage datetime2 output
AS
    
DECLARE @SQL nvarchar(max) = N'
SELECT @DateLastUsage = MAX(date_history) 
FROM v_inventory_hist
WHERE part = @Part;
';

DECLARE @proc nvarchar(1000) = QUOTENAME(@LinkedServer) + '..sys.sp_executesql';

EXEC @proc @SQL,
   N'@Part nvarchar(50),
     @DateLastUsage datetime2 output',

     @Part = @Part,
     @DateLastUsage = @DateLastUsage output;

Note also the change of data type to sysname and `nvarchar(max)1 respectively.