Menu
I have a lengthy stored procedure in which I would like to do something like the following:
IF @SubPageDirectory IS NULL
BEGIN
RAISERROR('@SubPageDirectory cannot be NULL', 10, 1)
EXIT STORED PROCEDURE
END
Basically I wish to check whether my variable is NULL, and if it is, return an error message to my .NET Web Application, and stop running the query at that stage. How can I achieve this?
827
How to return a custom error message from SQL Server in case of error? To return custom error message in case of error, we pass necessary parameter to the THROW statement. Throw has three parameters, ErrorNumber – must be greater than 50000 and less than 2147483647.
Therefore, when a system error occurs, SQL Server will log a system error and may take actions to fix the error. Custom errors, on the other hand, are generated by T-SQL custom codes based on your code or business logic. To add a custom error message to sys. messages, the stored procedure sp_addmessage is used.
When called in a CATCH block, ERROR_MESSAGE returns the complete text of the error message that caused the CATCH block to run. The text includes the values supplied for any substitutable parameters - for example, lengths, object names, or times. ERROR_MESSAGE returns NULL when called outside the scope of a CATCH block.
By itself, RAISERROR does not cause an exit from the procedure, but it can be combined with a RETURN statement or a test of the @@error global variable to control execution following a user-defined error. If you set the ON_TSQL_ERROR database option to CONTINUE, RAISERROR no longer signals an execution-ending error.
IF @SubPageDirectory IS NULL
BEGIN
RAISERROR('@SubPageDirectory cannot be NULL', 16, 1) --change to > 10
RETURN --exit now
END
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
