When I execute this query select 1/0 in sql server, it will show this Message:
Msg 8134, Level 16, State 1, Line 16 Divide by zero error encountered.
I need a function to get Number of error. for this example output of my function should be 8134
how can I create this function to get my required result?
CREATE FUNCTION GetErrorNumber
(
@command NVARCHAR(MAX)
)
RETURNS INT
AS
BEGIN
--function Body
END
If you are going to pass the error message to function, then try this:
CREATE FUNCTION Fun_GetErrorNumber
(
@command NVARCHAR(MAX)
)
RETURNS INT
AS
BEGIN
RETURN CAST(SUBSTRING(@q,PATINDEX('%[0-9]%',@q),PATINDEX('%,%',@q)-PATINDEX('%[0-9]%',@q)) AS INT)
END
But if you want to pass the query to function, you should take note that executing command inside Function is not valid and you need to use stored procedure instead. Like this:
CREATE PROCEDURE Pro_Execute
(
@command NVARCHAR(MAX) ,
@num INT OUTPUT
)
AS
BEGIN
BEGIN TRY
EXEC(@command);
SET @num = 0;
END TRY
BEGIN CATCH
SET @num = ERROR_NUMBER();
END CATCH;
END;
And call it like this:
DECLARE @res INT
EXEC Pro_Execute 'Select 1/0' ,@num = @res output
SELECT @res
The above SP will get the command and execute it. If non-fatal error occurred while executing the command, error number will be returned.
Reading this article may help you decorate your solution in better way
If I understand the question, you want to parse the error message and get the number between Msg and ,.
This seems like a simple string manipulation to me:
CREATE FUNCTION GetErrorNumber
(
@command nvarchar(max)
)
RETURNS int
AS
BEGIN
SELECT CAST(SUBSTRING(@command, 5, CHARINDEX(',', @command, 5) - 5) as int)
END
Though I do agree with juergen d - simply using the built in ERROR_NUMBER() seems better in most cases.
As described in this answer, You can't.
When you want to check your command you need to execute that command that it is impossible in body of a function.
