Error handler would not be running in case of sequence error

Question

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

Answer 1

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.

Answer 2

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 3

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 4

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.