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

Материал из SQL эксперт
Версия от 10:22, 26 мая 2010; Admin (обсуждение | вклад) (1 версия)
(разн.) ← Предыдущая | Текущая версия (разн.) | Следующая → (разн.)
Перейти к: навигация, поиск

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>
Источник — «http://sqle.ru/index.php?title=SQL_Server/T-SQL_Tutorial/Transact_SQL/RAISERROR&oldid=6365»