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

x33g5p2x 于 2023-08-02 发布在其他

关注(0)|答案(1)|浏览(124)

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

sql-server

来源：https://stackoverflow.com/questions/76815139/output-a-variable-from-stored-procedure-that-uses-dynamic-sql-and-openquery

1条答案

按热度按时间

wfveoks01#

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.

赞(0）回复(0）举报 2023-08-02

我来回答

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

1条答案

相关问题

热门标签

最新问答