This is my stored procedure which is called every two mins by a SQL Job.
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
All the various Invoicing USPs that this USP will execute have an output param @Return. The Invoicing USP's return either SUCCESS or ERROR... Details of error This has been tested and works as expected.
The problem is the above USP which calls an Invoicing USP dynamically is not getting the @Return output. I suspect my problem is with:
EXEC sp_executesql @ExecSql, @ParamOut, @ResultfromUSP = @Return out
I have even tried...
EXEC sp_executesql @ExecSql, @ParamOut, @ResultfromUSP out
but I can't seem to figure out why.
Any help appreciated.
UPDATE Thanks for all your comments... This usp is run by a SqlJob every 2 mins. So generally there is only ever one job that it needs to process occasionally there may be two. The Queue table has a primary key on ProcName so users cannot trigger the same Procedure until the one in the queue has finished. If they try they get a message back informing them that a job using this Procedure has been Queued or is In Progress and also gives the User name of who requested that job.
I had to implement this work queue flow as some of the Procs that are requested can themselves take 20 to 30mins to complete. Executing these directly from the client side meant the page could time out waiting for a response.
The procedure above works absolutely fine apart from I get no return from the EXEC sp_executesql …. and so the next bit of code always inserts the queued record into the completed table with a status of FAILED even though it actually was a SUCCESS.
I have got around this by having a switch statement that based on @ParamName does a hard coded EXEC usp_xxxxx instead of building the SQL dynamically and using EXEC sp_executesql….
