Error handler would not be running in case of sequence error

Question

Error handler would not be running in case of sequence error

Claudinei Aparecido da Silva 41

Group

      Because leaving my sequence name incorrect ( sqc_LogReplic_tmpx ) it would not be entering the error handling routine. And   adjusting to the correct name ( sqc_LogReplic_tmp ) and the routine executing the division by 0 instruction would it be falling into my error handling routine? ( See below. )

Bert Zhou-msft 3,516 Reputation points

2022-04-13T02:10:26.867+00:00

Hi，@Claudinei Aparecido da Silva
Welcome to Microsoft T-SQL Q&A Forum!

The information you provided is incomplete, is the database you are using SQL Server?
Is there any error message in the system after you change the sequence name? Please provide these to us so that we can better help you solve the problem.

Best regards,
Bert Zhou

Answer accepted by question author

3 additional answers

Your answer

Bert Zhou-msft 3,516 Reputation points

2022-04-13T02:10:26.867+00:00

Hi，@Claudinei Aparecido da Silva
Welcome to Microsoft T-SQL Q&A Forum!

The information you provided is incomplete, is the database you are using SQL Server?
Is there any error message in the system after you change the sequence name? Please provide these to us so that we can better help you solve the problem.

Best regards,
Bert Zhou

Answer 1

Hi，@Claudinei Aparecido da Silva

Welcome to Microsoft T-SQL Q&A Forum!

I don't think your description is very clear. What you are doing is just to create a sequence and use the NEXT VALUE FOR function to generate the sequence number,
but when the sequence name is wrong, an error will naturally occur (the reason is that the sequence does not exist, so how do you generate the correct one? )
In the second step, the icStatus you made is just an alias, print the value of the correct serial number incremented by 1 each time, and get 1...2.3.4.---10000. Here you use select 1/0 to mean again what? Do you want to display each incremented serial number divided by a value？

Best regards,
Bert Zhou

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Erland Sommarskog 127.4K MVP Volunteer Moderator

Yes, this is a known issue. It has been submitted as a bug to Microsoft more than once, and they have closed it as Won't Fix or even By Design.

The gist is this: SQL Server applies deferred name resolution. That is, when you create a stored procedure that refers to a non-existing object - table, view, or in this case a sequence - you don't get any error. SQL Server optimistically thinks that the object will exist at run time. Same thing when you start the procedure and a plan is created.

Eventually, when you arrive at the statement with the spelling error, SQL Server raises an error. Tragically, you cannot catch these compile errors at run time in the same scope as they occur. You can catch them in outer scopes, for instance a calling stored procedure.

Overall, error handling in SQL Server is a big mess.

Claudinei Aparecido da Silva 41 Reputation points

2022-04-26T03:01:09.1+00:00

ErlandSommarskog

Good night and I want to thank you for your information which was very valid.
And doing the tests I created the stored procedure that you indicated, but then I made the call by another stored procedure, there came the doubt because I was executing the main stored procedure by another, imagined that it is another, I would be able to capture the error message. Would the concept be something along these lines or really in no way will I be able to capture the error message? (I'm selling based on the example I sent.)

Erland Sommarskog 127.4K MVP Volunteer Moderator

Yes, you would be able. Look at this:

CREATE PROCEDURE inner_sp AS
BEGIN TRY
   SELECT NEXT VALUE FOR NoSuchSequence
END TRY
BEGIN CATCH
   PRINT 'This does not print'
END CATCH
go
CREATE PROCEDURE outer_sp AS
BEGIN TRY
   EXEC inner_sp
END TRY
BEGIN CATCH
   PRINT 'This prints!'
   PRINT error_message()
END CATCH
go
EXEC outer_sp
go
DROP PROCEDURE inner_sp, outer_sp

Answer 3

Claudinei Aparecido da Silva 41

Bert Zhou

I ended up not sending the code in the question, it follows below.

/*
CREATE SEQUENCE [dbo].[sqc_LogReplic_tmp]
AS [int]
START WITH 1
INCREMENT BY 1
MINVALUE 1
MAXVALUE 100000
CACHE
GO

*/

declare @pv_sqcTable int = 0

BEGIN TRY

select @pv_sqcTable = next value for sqc_LogReplic_tmpx


select @pv_sqcTable icStatus

SELECT 1/0  

 select 'Fim sem erro' icStatusError

END TRY
BEGIN CATCH

select 'Fim com erro' icStatusError

END CATCH

Answer 4

Tom Phillips 17,776

Just to add to Erland's response.

SQL Server error handling does NOT catch all runtime errors. It is not possible to CATCH an object does not exist error in SQL Server.

This is a huge problem which has never been addressed.

Erland Sommarskog 127.4K Reputation points MVP Volunteer Moderator

2022-04-14T20:59:18.513+00:00

It is not possible to CATCH an object does not exist error in SQL Server.

This is not correct. As I said above, you can catch these error outer scopes, but you cannot catch them in the scope they occur.
Tom Phillips 17,776 Reputation points

2022-04-14T21:06:15.93+00:00

True. Not at that level the OP is trying to catch it.

Share via

Error handler would not be running in case of sequence error

3 additional answers

Your answer