Home Is it possible to get output result set from SQL Server stored procedure before the trigger executes?

# Is it possible to get output result set from SQL Server stored procedure before the trigger executes?

DineshDB
1#
DineshDB Published in 2018-01-11 06:48:33Z
 I have a main table Din_Test_Procedure: CREATE TABLE Din_Test_Procedure(Emp_ID INT);  And an audit table for the table is: CREATE TABLE Din_Test_Procedure_Audit(Emp_ID INT,LoopingValue BIGINT);  And I write a trigger for the table, when an INSERT occurs, 1 million records insert into the audit table: CREATE TRIGGER TrgAfterInsert_SPTest ON [dbo].[Din_Test_Procedure] FOR INSERT AS DECLARE @CurID INT, @MIN INT = 1, @MAX INT = 1000000 SELECT @CurID = i.Emp_ID FROM inserted i; WHILE @MIN <= @MAX BEGIN INSERT INTO Din_Test_Procedure_Audit VALUES(@CurID, @MIN) SET @MIN = @MIN + 1 END GO  And also I create a stored procedure with an OUTPUT parameter to insert record into the main table. CREATE PROCEDURE Din_Insert_SPTest @ID INT, @ErrCode INT OUTPUT AS BEGIN INSERT INTO Din_Test_Procedure SELECT @ID SELECT @ErrCode = 0 END  When I execute the procedure, it shows the OUTPUT after the trigger execution completes. It took some time delay to give the output. DECLARE @ErrCode INT EXEC Din_Insert_SPTest 1,@ErrCode OUTPUT SELECT @ErrCode  Is it possible to get the output once the main table insertion completes, which means get the output before the trigger process completes.
Zohar Peled
2#
Zohar Peled Reply to 2018-01-11 07:51:00Z
 No. SQL Server will return control to the stored procedure only after the trigger has completed it's run. This means that SELECT @ErrCode = 0 will only happen after the trigger was executed, and of course, you can only get the results back from the stored procedure after it's completed it's run. The reason for this is that the trigger and statement that fires it are executed inside an implicit transaction. For more information, see Microsoft's DML Triggers documentation
gotqn
3#
 You are generating a sequence of numbers in a loop. Then every value is inserted in a table. In other words, your code is handle the data row by agonizing row. You can try to optimize your code. For example, in your trigger use the following technique to insert the sequence: CREATE TRIGGER TrgAfterInsert_SPTest ON [dbo].[SP_Test_Procedure] FOR INSERT AS DECLARE @CurID INT, @MIN INT=1, @MAX INT=1000000 SELECT @CurID=i.Emp_ID FROM inserted i; INSERT INTO SP_Test_Procedure_Audit VALUES(@CurID,@MIN) SELECT TOP (@Max-@Min+1) @CurID, @Min-1+row_number() over(order by t1.number) as N FROM master..spt_values t1 CROSS JOIN master..spt_values t2 GO  You can also check this answer and this article of other alternatives to generate sequence. If this is not enough, you can remove the trigger and scheduled the insertion with a delay if this is OK for your business needs.