I have a stored procedure that has 2 flaws:
If there is an error in the stored procedure, it keeps executing. I want the stored procedure to stop immediately once there is any error found.
There is an error log table for this stored procedure, I have customized the error and if the error do happen, it repeats the same error message multiple times because of the while loop used in the stored procedure. I want the error to be logged only once per occurrence.
This is the general outline of the stored procedure code:
ALTER PROCEDURE [sp_abc]
AS
BEGIN
create table #abc1 (id int, name char(20), done bit default 0)
values (1,'ben'), (2,'charles')
WHILE (SELECT count(*) from #abc1 where done = 0) > 0
BEGIN
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO abc123 (col1, col2)
VALUES (1,2), (3,4)
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @error_number INT
, @error_message VARCHAR(MAX)
SET @error_number = ERROR_NUMBER()
SET @error_message = ERROR_MESSAGE() + ' [Trying to insert data into abc123]'
ROLLBACK TRANSACTION
-- Insert error into an error table
INSERT INTO [dbo].[error_log] ( sp_name,[error_message] )
SELECT DISTINCT ' sp123 ', @error_message
END CATCH
END