Technical Article

Function to get a valid user error number

,

This function allows you to capture the next valid error number to use in the sp_addmessage sp. No matter hoy much user errors you have define, you will always get the next available error number.

GO
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'fn_NroError' AND xtype = 'FN')
DROP FUNCTION fn_NroError
GO
CREATE FUNCTION fn_NroError
()
RETURNS INT
AS
BEGIN
/* Capture the last error number */DECLARE @ErrorNro AS INT
SELECT @ErrorNro = MAX(Error) FROM master.dbo.sysmessages

/* if it is minor that 50000 then it is a system error, so I use 50001 */IF @ErrorNro < 50000
SELECT @ErrorNro = 50001
/* if it is bigger then I use the next one */ELSE
SELECT @ErrorNro = @ErrorNro + 1

/* returns the value */RETURN @ErrorNro
END
GO

Rate

Share

Share

Rate