In the last post we seen how to manage UDF errors (Add , Update , delete)  UDF error messages

in SYS.MESSAGES catalog. In this post we will look in-detail in to the Use RAISERROR function

in SQL Server to raise UDF errors. When I decided to write this, I then remembered that I written

a post for SQL server 2o12 Throw statement which will explain the differences of  raiserror and throw

, disadvantages of raiserrors and how to implement raiserror and throw, and how to raise UDF

errors , so have look at this post

https://sqljunkieshare.com/2011/12/10/throw-statement-tsql-2012/

but I will go through  some aspects of Raiserror that I didn’t cover  in the above post

lets start with syntax of raiseerror

RAISERROR ( { msg_id | msg_str | @local_variable }
{,severity,state }
[,argument [,…n ] ] )
[ WITH option [,…n ] g

We talked about Msg_id in the earlier post, we will look at the msg_str

msg_str

Is a user-defined message with formatting similar to the printf function in the C standard library. The error message can have a maximum of 2,047 characters. If the message contains 2,048 or more characters, only the first 2,044 are displayed and an ellipsis is added to indicate that the message has been truncated.

 

msg_str is a string of characters with optional embedded conversion specifications. Each conversion specification defines how a value in the argument list is formatted and placed into a field at the location of the conversion specification in msg_str. Conversion specifications have this format:

% [[flag] [width] [. precision] [{h | l}]] type

flag

Is a code that determines the spacing and justification of the substituted value.

Code	Prefix or justification	Description
– (minus)	Left-justified	Left-justify the argument value within the given field width.
+ (plus)	Sign prefix	Preface the argument value with a plus (+) or minus (-) if the value is of a signed type.
0 (zero)	Zero padding	Preface the output with zeros until the minimum width is reached. When 0 and the minus sign (-) appear, 0 is ignored.
# (number)	0x prefix for hexadecimal type of x or X	When used with the o, x, or X format, the number sign (#) flag prefaces any nonzero value with 0, 0x, or 0X, respectively. When d, i, or u are prefaced by the number sign (#) flag, the flag is ignored.
‘ ‘ (blank)	Space padding	Preface the output value with blank spaces if the value is signed and positive. This is ignored when included with the plus sign (+) flag.

width

Is an integer that defines the minimum width for the field into which the argument value is placed. If the length of the argument value is equal to or longer than width, the value is printed with no padding. If the value is shorter than width, the value is padded to the length specified in width.

An asterisk (*) means that the width is specified by the associated argument in the argument list, which must be an integer value.

precision

Is the maximum number of characters taken from the argument value for string values. For example, if a string has five characters and precision is 3, only the first three characters of the string value are used.

For integer values, precision is the minimum number of digits printed.

An asterisk (*) means that the precision is specified by the associated argument in the argument list, which must be an integer value.

{h | l} type

Is used with character types d, i, o, s, x, X, or u, and creates shortint (h) or longint (l) values.

Type specification	Represents
d or i	Signed integer
o	Unsigned octal
s	String
u	Unsigned integer
x or X	Unsigned hexadecimal

 

option

Is a custom option for the error and can be one of the values in the following table.

Value	Description
LOG	Logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine. Errors logged in the error log are currently limited to a maximum of 440 bytes. Only a member of the sysadmin fixed server role or a user with ALTER TRACE permissions can specify WITH LOG.
NOWAIT	Sends messages immediately to the client.
SETERROR	Sets the @@ERROR and ERROR_NUMBER values to msg_id or 50000, regardless of the severity level.

 

 

Note:

Raiserror will not work if it is used

• Outside the scope of any TRY block.
• With a severity of 10 or lower in a TRY block.
• With a severity of 20 or higher that terminates the database connection.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Code:

DECLARE @LOGINNAME VARCHAR(50) = ‘testuser’
DECLARE @SERVER VARCHAR(50) = ‘testserver’
–SELECT @LOGINNAME = ORIGINAL_LOGIN()
–A custom error message using arguments
RAISERROR (‘This is a custom error message.
Login: %s,
Language: %s,
SPID: %u,
Server Name: %s’, 5,1,
@LOGINNAME, @@LANGUAGE, @@SPID, @SERVER) WITH LOG