SQL Server/T-SQL Tutorial/Transact SQL/RAISERROR
Содержание
- 1 Raise error in case of error
- 2 Raise error out of a procedure
- 3 Raise exception with parameters
- 4 RAISERROR ( -1, @parm1, @parm2)
- 5 RAISERROR syntax
- 6 Raising a message not defined in sysmessages.
- 7 The syntax of the RAISERROR statement: RAISERROR ({message_id|message_string}, severity, state [, argument]...)
- 8 Using RAISERROR without an Error Number
- 9 Using RAISERROR with the SETERROR Option
Raise error in case of error
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>
Raise error out of a procedure
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
Raise exception with parameters
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>
RAISERROR ( -1, @parm1, @parm2)
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>
RAISERROR syntax
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.
Raising a message not defined in sysmessages.
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
The syntax of the RAISERROR statement: RAISERROR ({message_id|message_string}, severity, state [, argument]...)
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
Using RAISERROR without an Error Number
3>
4> RAISERROR ("we have a problem.", 16, 1)
5>
Using RAISERROR with the SETERROR Option
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>