SQL Server/T-SQL Tutorial/Transact SQL/RAISERROR

Материал из SQL эксперт
Перейти к: навигация, поиск

Raise error in case of error

   <source lang="sql">

6> DECLARE @sql AS NVARCHAR(4000), 7> @b AS VARBINARY(1000), @s AS VARCHAR(2002); 8> SET @s = "0x0123456789abcdef"; 9> 10> IF @s NOT LIKE "0x%" OR @s LIKE "0x%[^0-9a-fA-F]%" 11> BEGIN 12> RAISERROR("Possible SQL Injection attempt.", 16, 1); 13> RETURN; 14> END 15> 16> SET @sql = N"SET @o = " + @s + N";"; 17> EXEC sp_executesql 18> @stmt = @sql, 19> @params = N"@o AS VARBINARY(1000) OUTPUT", 20> @o = @b OUTPUT; 21> 22> SELECT @b; 23> GO




0x0123456789ABCDEF

1> 2></source>


Raise error out of a procedure

   <source lang="sql">

13> create table Billings ( 14> BankerID INTEGER, 15> BillingNumber INTEGER, 16> BillingDate datetime, 17> BillingTotal INTEGER, 18> TermsID INTEGER, 19> BillingDueDate datetime , 20> PaymentTotal INTEGER, 21> CreditTotal INTEGER 22> 23> ); 24> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> CREATE PROC spInsertBilling 4> @BankerID int, @BillingNumber varchar(50), 5> @BillingDate smalldatetime, @BillingTotal money, 6> @TermsID int, @BillingDueDate smalldatetime 7> AS 8> IF EXISTS(SELECT * FROM Billings WHERE BankerID = @BankerID) 9> BEGIN 10> INSERT Billings (BankerID) 11> VALUES (@BankerID) 12> END 13> ELSE 14> BEGIN 15> RAISERROR("Not a valid BankerID!",1,1) 16> RETURN -100 17> END 18> GO 1> 2> 3> DECLARE @ReturnVar int 4> EXEC @ReturnVar = spInsertBilling 5> 799,"ZXK-799","2002-07-01",299.95,1,"2001-08-01" 6> PRINT "Return code was: " + CONVERT(varchar,@ReturnVar) 7> GO Not a valid BankerID! Return code was: -100 1> 2> drop PROC spInsertBilling; 3> GO 1> 2> 3> drop table Billings; 4> GO</source>


Raise exception with parameters

   <source lang="sql">

6> DECLARE @ProductId INT 7> SET @ProductId = 100 8> 9> 10> RAISERROR("Problem with ProductId %i", 16, 1, @ProductId) 11> GO Msg 50000, Level 16, State 1, Server J\SQLEXPRESS, Line 10 Problem with ProductId 100 1></source>


RAISERROR ( -1, @parm1, @parm2)

   <source lang="sql">

28> DECLARE @parm1 varchar(30), @parm2 int 29> SELECT @parm1=USER_NAME(), @parm2=@@spid 30> RAISERROR (50001, 15, -1, @parm1, @parm2) 31> GO Msg 50001, Level 15, State 1, Server J\SQLEXPRESS, Line 30 The specified value for dbo was invalid. 1></source>


RAISERROR syntax

   <source lang="sql">

RAISERROR ({msg_id | msg_str}, severity, state[, argument1 [, argumentn]]) [WITH LOG]|[WITH NOWAIT] RAISERROR Options Value Description LOG Logs the error in the SQL Server error log and the application log. NOWAIT Sends messages immediately to the client. SETERROR Sets @@ERROR value to msg_id or 50000, regardless of the severity level.</source>


Raising a message not defined in sysmessages.

   <source lang="sql">

6> DECLARE @chrPrintMsg CHAR(255) 7> 8> RAISERROR("Undefined error raised using the WITH SETERROR option",1,2) WITH SETERROR 9> 10> SELECT @chrPrintMsg = "Using WITH SETERROR sets the error number generated to " + CONVERT(char,@@error) 11> PRINT @chrPrintMsg 12> GO Undefined error raised using the WITH SETERROR option Using WITH SETERROR sets the error number generated to 50000</source>


The syntax of the RAISERROR statement: RAISERROR ({message_id|message_string}, severity, state [, argument]...)

   <source lang="sql">

13> create table Billings ( 14> BankerID INTEGER, 15> BillingNumber INTEGER, 16> BillingDate datetime, 17> BillingTotal INTEGER, 18> TermsID INTEGER, 19> BillingDueDate datetime , 20> PaymentTotal INTEGER, 21> CreditTotal INTEGER 22> 23> ); 24> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> CREATE PROC spInsertBilling 4> @BankerID int, @BillingNumber varchar(50), 5> @BillingDate smalldatetime, @BillingTotal money, 6> @TermsID int, @BillingDueDate smalldatetime 7> AS 8> IF EXISTS(SELECT * FROM Bankers WHERE BankerID = @BankerID) 9> BEGIN 10> INSERT Billings (BankerID) 11> VALUES (@BankerID) 12> END 13> ELSE 14> BEGIN 15> RAISERROR("Not a valid BankerID!",1,1) 16> RETURN -100 17> END 18> GO 1> 2> 3> drop PROC spInsertBilling; 4> GO 1> 2> 3> drop table Billings; 4> GO</source>


Using RAISERROR without an Error Number

   <source lang="sql">

3> 4> RAISERROR ("we have a problem.", 16, 1) 5></source>


Using RAISERROR with the SETERROR Option

   <source lang="sql">

6> sp_addmessage 7> 60000, 8> 16, 9> "Unable to find ID %09d" 10> GO Msg 15043, Level 16, State 1, Server BCE67B1242DE45A\SQLEXPRESS, Procedure sp_addmessage, Line 137 You must specify "REPLACE" to overwrite an existing message. 1> 2> 3> RAISERROR (60000, 1, 2) 4> SELECT @@ERROR 5> GO Unable to find ID (null)


         0

(1 rows affected) 1> RAISERROR (60000, 1, 2) WITH SETERROR 2> SELECT @@ERROR 3> 4> sp_dropmessage 5> 60000 6> GO Msg 102, Level 15, State 1, Server BCE67B1242DE45A\SQLEXPRESS, Line 5 Incorrect syntax near "60000". 1></source>