T sql raiserror function

t sql raiserror function

The RAISERROR statement allows you to generate your own error messages and return these messages back to the application using the same format as a system error. SQL Server RAISERROR Statement · WITH LOG – shows the message to the user and log the error in the error logs. · WITH SETERROR – automatically sets the. The RAISERROR statement is a Transact-SQL statement for generating user-defined errors. It has a similar function to the SIGNAL statement.

T sql raiserror function - thank

Summary: in this tutorial, you will learn how to use the SQL Server statement to generate user-defined error messages.

If you develop a new application, you should use the statement instead.

SQL Server statement overview

The statement allows you to generate your own error messages and return these messages back to the application using the same format as a system error or warning message generated by SQL Server Database Engine. In addition, the statement allows you to set a specific message id, level of severity, and state for the error messages.

The following illustrates the syntax of the statement:

Code language:SQL (Structured Query Language)(sql)

Let’s examine the syntax of the for better understanding.

message_id

The is a user-defined error message number stored in the catalog view.

To add a new user-defined error message number, you use the stored procedure. A user-defined error message number should be greater than 50,000. By default, the statement uses the 50,000 for raising an error.

The following statement adds a custom error message to the view:

Code language:SQL (Structured Query Language)(sql)

To verify the insert, you use the following query:

Code language:SQL (Structured Query Language)(sql)

To use this message_id, you execute the statement as follows:

Code language:SQL (Structured Query Language)(sql)

Here is the output:

Code language:SQL (Structured Query Language)(sql)

To remove a message from the , you use the stored procedure . For example, the following statement deletes the message id 50005:

Code language:SQL (Structured Query Language)(sql)

message_text

The is a user-defined message with formatting like the function in C standard library. The can be up to 2,047 characters, 3 last characters are reserved for ellipsis (…). If the contains 2048 or more, it will be truncated and is padded with an ellipsis.

When you specify the , the statement uses message_id 50000 to raise the error message.

The following example uses the statement to raise an error with a message text:

Code language:SQL (Structured Query Language)(sql)

The output will look like this:

Code language:SQL (Structured Query Language)(sql)

severity

The severity level is an integer between 0 and 25, with each level representing the seriousness of the error.

Code language:SQL (Structured Query Language)(sql)

state

The state is an integer from 0 through 255. If you raise the same user-defined error at multiple locations, you can use a unique state number for each location to make it easier to find which section of the code is causing the errors. For most implementations, you can use 1.

WITH option

The option can be , , or :

  • logs the error in the error log and application log for the instance of the SQL Server Database Engine.
  • sends the error message to the client immediately.
  • sets the and values to message_id or 50000, regardless of the severity level.

SQL Server examples

Let’s take some examples of using the statement to get a better understanding.

A) Using SQL Server with block example

In this example, we use the inside a block to cause execution to jump to the associated block. Inside the block, we use the to return the error information that invoked the block.

Code language:SQL (Structured Query Language)(sql)

Here is the output:

Code language:SQL (Structured Query Language)(sql)

B) Using SQL Server statement with a dynamic message text example

The following example shows how to use a local variable to provide the message text for a statement:

Code language:SQL (Structured Query Language)(sql)

The output is as follows:

Code language:SQL (Structured Query Language)(sql)

When to use statement

You use the statement in the following scenarios:

  • Troubleshoot Transact-SQL code.
  • Return messages that contain variable text.
  • Examine the values of data.
  • Cause the execution to jump from a block to the associated block.
  • Return error information from the block to the callers, either calling batch or application.

In this tutorial, you will learn how to use the SQL Server statement to generate user-defined error messages.

Using the RAISERROR statement in procedures

The RAISERROR statement is a Transact-SQL statement for generating user-defined errors. It has a similar function to the SIGNAL statement.

For a description of the RAISERROR statement, see "RAISERROR statement [T-SQL]" in Reference: Statements and Options.

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. Instead, the procedure completes and stores the RAISERROR status code and message, and returns the most recent RAISERROR. If the procedure causing the RAISERROR was called from another procedure, RAISERROR returns after the outermost calling procedure terminates.

You lose intermediate RAISERROR statuses and codes when the procedure terminates. If, at return time, an error occurs along with RAISERROR, the error information is returned and you lose the RAISERROR information. The application can query intermediate RAISERROR statuses by examining @@error global variable at different execution points.

A common programming task is to create a user-defined function. The function can perform some tasks and raise an error if some validation fails.
Let us try to raise an error
CREATE FUNCTION dbo.Division(@op1 INT, @op2 INT) RETURNS INT AS BEGIN IF (@op2 = 0) BEGIN RAISERROR ('Division by zero.', 16, 1); END RETURN CAST(@op1 AS DECIMAL(18, 8)) /@op2 END
But this approach gives the following error:

Msg 443, Level 16, State 14, Procedure Division, Line 7 [Batch Start Line 0]
Invalid use of a side-effecting operator 'RAISERROR' within a function.

So another approach is needed.
Let us try the THROW statement
CREATE FUNCTION dbo.Division(@op1 INT, @op2 INT) RETURNS INT AS BEGIN IF (@op2 = 0) BEGIN ;THROW 51000, 'Division by zero.',1; END RETURN CAST(@op1 AS DECIMAL(18, 8)) /@op2 END
But the result is the same as the RAISERROR:

Msg 443, Level 16, State 14, Procedure Division, Line 7 [Batch Start Line 0]
Invalid use of a side-effecting operator 'THROW' within a function.

Having tried all the known solutions to throw an exception in Sql Server, an alternative is needed.
Let's try a cast conversion error:
CREATE FUNCTION dbo.Division(@op1 INT, @op2 INT) RETURNS DECIMAL(18, 8) AS BEGIN IF (@op2 = 0) BEGIN RETURN CAST('Division by zero.' AS INT); END RETURN CAST(@op1 AS DECIMAL(18, 8)) / CAST(@op2 AS DECIMAL(18, 8)) END
The function is now created with success.
Let us see the result of calling the function with a division by zero:
SELECT dbo.Division(1, 0)
The following exception is thrown:

Msg 245, Level 16, State 1, Line 14
Conversion failed when converting the varchar value 'Division by zero.' to data type int.


It's not the ideal solution, but it allows us to generate a exception on a T-SQL function.

SA0144 : The code following the RETURN or the RAISERROR statements will never be executed

The topic describes the SA0144 analysis rule.

Message

The code following the RETURN or the RAISERROR statements will never be executed

Description

The rule checks T-SQL script and reports for dead code which will never be executed.

The rule checks stored procedures, functions and triggers for unreachable code following unconditional RETURN statement, after RAISERROR with severity > 10 or after RAISERROR inside BEGIN/END TRY block.

1CREATEPROCEDURE [dbo].[test_DeadCode_Rule] 2( 3 @Value1 BIGINT 4) 5AS 6 7IF (@Value1 = 0) 8BEGIN 9RETURN010END11ELSE12BEGIN13RETURN @Value1 + 114END1516RETURN-1; 1718PRINT'This message will never be printed'
How to fix

To fix the rule violation, review your code and remove the dead code.

Scope

The rule has a Batch scope and is applied only on the SQL script.

Parameters
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix

20 minutes per issue.

Categories

Design Rules, Code Smells

Additional Information
Example Test SQL
1ALTERPROCEDURE [dbo].[test_DeadCode_Rule] 2( [email protected] INT = 0 4) 5AS 6BEGIN 7 8BEGIN TRY 9RAISERROR('@Param1 should not be null',11, 111); 10PRINT'dead code'11END TRY 12BEGIN CATCH 13SET @Param1 = 114END CATCH 1516IF (@Param1 > 0) 17BEGIN18RETURN100/@Param1; 19PRINT'This statement will not be executed'; 20END21IF (@Param1 < 0) 22BEGIN23RAISERROR ('Raise error instead of using RETURN statement.', 2,1); 24PRINT'This statement will not be executed'; 25END26END
Analysis Results
MessageLineColumn
1SA0144 : The code following the RAISERROR statement will never be executed.92
2SA0144 : The code following the RETURN statement will never be executed.182
See Also

Back to: SQL Server Tutorial For Beginners and Professionals

RaiseError and @@ERROR function in SQL Server with Example

In this article, I am going to discuss the RaiseError and @@ERROR Function in SQL Server with Example. Please read our previous article where we discussed the Basics Concepts of Exception Handling in SQL Server with examples.

RaiseError Function in SQL Server

The system-defined Raiserror() function returns an error message back to the calling application. The RaiseError System defined Function in SQL Server takes 3 parameters as shown below. 
RAISERROR(‘Error Message’, ErrorSeverity, ErrorState)

  1. Error Message: The custom error message that you want to display whenever the exception is raised.
  2. Error Severity: When we are returning any custom errors in SQL Server, we need to set the ErrorSeverity level as 16, which indicates this is a general error and this error can be corrected by the user. In our example, the error can be corrected by the user by giving a nonzero value for the second parameter.
  3. Error State: The ErrorState is also an integer value between 1 and 255. The RAISERROR() function can only generate custom errors if you set the Error State value between 1 to 127.
@@Error System Function in SQL Server:

In SQL Server 2000, in order to detect errors, we use the @@Error system function. The @@Error system function returns a NON-ZERO value if there is an error, otherwise, ZERO indicates that the previous SQL statement was executed without any error. 

Example: RaiseError and @@ERROR Function in SQL Server

We are going to use the following Product and ProductSales table to understand how to handle errors in SQL Server using RaiseError and @ERROR System-Defined Functions.

RaiseError and @@ERROR function in SQL Server with Example

Please use the below SQL Script to create and populate the Product and ProductSales table with sample data.

-- Create Product table CREATE TABLE Product ( ProductId INT PRIMARY KEY, Name VARCHAR(50), Price INT, QuantityAvailable INT ) GO -- Populate the Product Table with some test data INSERT INTO Product VALUES(101, 'Laptop', 1234, 100) INSERT INTO Product VALUES(102, 'Desktop', 3456, 50) INSERT INTO Product VALUES(103, 'Tablet', 5678, 35) INSERT INTO Product VALUES(104, 'Mobile', 7890, 25) GO -- Create ProductSales table CREATE TABLE ProductSales ( ProductSalesId INT PRIMARY KEY, ProductId INT, QuantitySold INT ) GO -- Populate the ProductSales Table with some test data INSERT INTO ProductSales VALUES(1, 101, 5) INSERT INTO ProductSales VALUES(2, 102, 7) INSERT INTO ProductSales VALUES(3, 103, 5) INSERT INTO ProductSales VALUES(4, 104, 7) Go
Create the following stored procedure for product sales.

The following stored procedure accepts 2 parameters – ProductID and QuantityToSell. The ProductID parameter specifies the product that we want to sell and the QuantityToSell parameter specifies the quantity that we want to sell. In the below procedure, if enough stock is not available then we are raising a custom exception by using the Raiserror statement.

The problem with the above-stored procedure is that the transaction is always going to be committed even though there is an error somewhere between updating the Product table and inserting data into the ProductSales table.

The main purpose of wrapping these 2 statements (Update Product Statement and Insert into ProductSales statement) in a transaction is to ensure that, both of these statements are treated as a single unit. For example, if we have an error when executing the second statement, then the first statement should be rolled back.  

Let us modify the stored procedure to use the @@ERROR function to check if there any error occurred. If no error occurred then we are committing the transaction else we roll backing the transaction.

In the above procedure, if you comment the line (Set @MaxProductSalesId = @MaxProductSalesId + 1), and then execute the stored procedure there will be a primary key violation error when trying to insert into the ProductSales table as a result of which the entire transaction will be rolled back.

Note: The @@ERROR is cleared and reset on each statement execution. Check it immediately following the statement being verified, or save it to a local variable that can be checked later.

In the Product table, we already have a record with ProductID = 4. So the insert statement causes a primary key violation error. The @@ERROR retains the error number, as we are checking for it immediately after the statement that causes the error.

INSERT INTO Product values(4, 'Mobile Phone', 1500, 100) IF(@@ERROR <> 0) PRINT 'Error Occurred' ELSE PRINT 'No Errors'

On the other hand, when you execute the code below, you will get the message ‘No Errors’. This is because the @@ERROR is cleared and reset on each statement execution. 

INSERT INTO Product values(4, 'Mobile Phone', 1500, 100) -- At this point the @@ERROR will have a NON ZERO value SELECT * FROM Product -- At this point the @@ERROR reset to ZERO, because the -- select statement successfully executed IF(@@ERROR <> 0) PRINT 'Error Occurred' ELSE PRINT 'No Errors'

In the below example, we are storing the value of the @@Error function to a local variable, which is used later.

DECLARE @Error INT INSERT INTO Product VALUES(4, 'Mobile Phone', 1500, 100) Set @Error = @@ERROR SELECT * FROM Product IF(@Error <> 0) PRINT 'Error Occurred' ELSE PRINT 'No Errors'

In the next article, I am going to discusshow to raise errors explicitly in SQL Server, and also we will discuss the different options that we can use with the Raiserror function. Here, in this article, I try to explain the RaiseError and @@Error Function in SQL Server along with @@ERROR with Examples. I hope you enjoy this RaiseError in SQL Server with Examples article.

SQL Syntax

RAISERROR

In This Topic

Generates an error message with text or a number and throws an exception back to the hosting application if the script does not catch it. This behavior is slightly different than SQL Server.

It is a best practice to always put raiserror calls in a try catch block in the sql script. This will ensure the same behavior between VistaDB and SQL Server.

RAISERROR ( { msg_id

Share: T sql raiserror function

Ts error mpeg-4 descriptor not found
Saa7133[0] dsp access error
Error 017 undefined symbol logs samp
Canon mp600 error code 5010
Contr terrorism simbian 9.4
t sql raiserror function

0 Comments

Leave a Comment