sql-server - 执行动态 SQL 以执行 Stored Proc 并获取结果字符串

Question

这是我的存储过程，SQL 作业每两分钟调用一次。

    USE [MyDB]
GO
/****** Object:  StoredProcedure [dbo].[usp_Process_Invoice_USPs]    Script Date: 27/03/2019 11:39:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ==================================================
-- Author:      Mych
-- Create date: 20/03/2019
-- Description: This stored procedure will create
-- an in-memory table and get any uncompleted rows
-- from the Queue table. For each row found it
-- will construct an Exec statement and execute it.
-- it then marks the row as completed.
-- =================================================
ALTER PROCEDURE [dbo].[usp_Process_Invoice_USPs] 

AS

DECLARE @QueueID INT,
    @ProcName NVARCHAR(100),
    @Username NVARCHAR(50),
    @UserEmail NVARCHAR(100),
    @ParamName NVARCHAR(50),
    @ParamValue INT,
    @QueuedOn Datetime,
    @ParamOut NVarchar(200),
    @RowCnt INT,
    @MaxRows INT,
    @ExecSql NVARCHAR(Max),
    @ResultfromUSP NVARCHAR(250),
    @Return NVARCHAR(250)

SELECT @RowCnt = 1

-- Create an in-memory table - Doing this as this is the only way I can think of to guarantee that rownum will be consecutive
-- the ID column in Tbl_Invoicing_Queue is an Identity column but numbers can be missed out when an insert fails for some reason.

DECLARE @Import TABLE (rownum INT IDENTITY (1, 1) PRIMARY KEY NOT NULL , QueueID INT NOT NULL, ProcName NVARCHAR(100), 
Username NVARCHAR(50), UserEmail NVARCHAR(100), ParamName NVARCHAR(50), ParamValue int, QueuedOn datetime)
-- Insert any uncompleted rows (Progress = 'Queued') from the Queue to the in-memory table
INSERT INTO @Import (QueueID, ProcName, Username, UserEmail, ParamName, ParamValue, QueuedOn) 
SELECT ID, ProcName, Username, UserEmail, ParamName, ParamValue, QueuedOn FROM TBL_Invoicing_Queue WHERE Progress = 'Queued'

-- Find out how many rows were imported.
SELECT @MaxRows=count(*) FROM @Import

--loop through each row... get the field values and construct a sql Exec statement based on the values
--Execute the Exec Statement 
--Write all details and result to the completed table... Progress will be either completed or failed dependant of @ResultfromUSP
--Delete the row from the queued table.

while @RowCnt <= @MaxRows
begin
    SELECT @QueueID = QueueID FROM @Import WHERE rownum = @RowCnt;
    SELECT @ProcName = ProcName FROM @Import WHERE rownum = @RowCnt;
    SELECT @UserName = UserName FROM @Import WHERE rownum = @RowCnt;
    SELECT @UserEmail = UserEmail FROM @Import WHERE rownum = @RowCnt;
    SELECT @ParamName = ParamName FROM @Import WHERE rownum = @RowCnt;
    SELECT @ParamValue = ParamValue FROM @Import WHERE rownum = @RowCnt;
    SELECT @QueuedOn = QueuedOn FROM @Import WHERE rownum = @RowCnt

    Update TBL_Invoicing_Queue SET Progress = 'In Progress - ' + CONVERT(VARCHAR, FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss', 'en-GB')) Where ID = @QueueID

    SET @ExecSql =  N'EXEC dbo.' + @ProcName

    If @ParamName <> ''
    BEGIN
        SET @ParamOut = N'@User = ''' + @UserName + ''', @EmailAddress = ''' + @UserEmail + ''', @' + @ParamName + ' = ' + CAST(@ParamValue AS NVARCHAR(20))
    END
    ELSE
    BEGIN
        SET @ParamOut = N'@User = ''' + @UserName + ''', @EmailAddress = ''' + @UserEmail + ''', @Return = @Return out'
    END

    --Execute our exec statement and get returned result (string) which will either be SUCCESS or a message detailing a failure.
    EXEC sp_executesql @ExecSql, @ParamOut, @ResultfromUSP = @Return out

    --dependant on Result we insert the following into the completed table
    SELECT @ResultfromUSP
    PRINT @ResultfromUSP
    PRINT 'Got result back from USP'

    IF @ResultfromUSP = 'SUCCESS' 
    BEGIN
        Insert INTO TBL_Invoicing_Completed (QID, ProcName, Username, UserEmail, ParamName, ParamValue, QueuedOn, Progress, Result) Values 
        (@QueueID, @ProcName, @UserName, @UserEmail, @ParamName, @ParamValue, @QueuedOn, 'Completed - ' + CONVERT(VARCHAR, FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss', 'en-GB')), @ResultfromUSP)
    END
    ELSE
    BEGIN
        Insert INTO TBL_Invoicing_Completed (QID, ProcName, Username, UserEmail, ParamName, ParamValue, QueuedOn, Progress, Result) Values 
        (@QueueID, @ProcName, @UserName, @UserEmail, @ParamName, @ParamValue, @QueuedOn, 'FAILED - ' + CONVERT(VARCHAR, FORMAT(GETDATE(), 'dd/MM/yyyy HH:mm:ss', 'en-GB')), @ResultfromUSP)
    END
    -- remove the row from the queued table
    DELETE FROM TBL_Invoicing_Queue Where ID = @QueueID
    -- increment the count and loop to next row
    Select @RowCnt = @RowCnt + 1

end

-- NO need to drop @Import as this is cleared automatically

该 USP 将执行的所有各种 Invoicing USP 都有一个输出参数 @Return。发票 USP 返回 SUCCESS 或 ERROR... 错误详细信息 这已经过测试并按预期工作。

问题是上述动态调用 Invoicing USP 的 USP 没有获得 @Return 输出。我怀疑我的问题在于：

EXEC sp_executesql @ExecSql, @ParamOut, @ResultfromUSP = @Return out

我什至试过...

EXEC sp_executesql @ExecSql, @ParamOut, @ResultfromUSP out

但我似乎无法弄清楚为什么。

任何帮助表示赞赏。

更新 感谢您的所有评论...此 usp 由 SqlJob 每 2 分钟运行一次。所以通常只有一个工作需要处理，偶尔可能有两个。队列表在 ProcName 上有一个主键，因此用户不能触发相同的过程，直到队列中的过程完成。如果他们尝试，他们会收到一条消息，通知他们使用此过程的作业已排队或正在进行中，并且还会提供请求该作业的用户名。

我必须实现这个工作队列流程，因为一些请求的 Procs 本身可能需要 20 到 30 分钟才能完成。直接从客户端执行这些意味着页面可能会超时等待响应。

除了我没有从 EXEC sp_executesql 那里得到任何回报之外，上面的过程工作得很好。因此下一段代码总是将排队的记录插入到状态为 FAILED 的已完成表中，即使它实际上是成功的。

我通过使用基于@ParamName 的 switch 语句来解决这个问题，它执行硬编码的 EXEC usp_xxxxx，而不是动态构建 SQL 并使用 EXEC sp_executesql ...。

标签： sql-servertsqlstored-proceduresoutput-parameter