SQL Server/T-SQL Tutorial/Transact SQL/Goto

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

Goto statement

   <source lang="sql">

2> 3> CREATE TABLE a ( 4> a char(1) primary key) 5> 6> CREATE TABLE b ( 7> b char(1) references a) 8> 9> CREATE TABLE c ( 10> c char(1)) 11> GO 1> 2> CREATE PROC test as 3> BEGIN TRANSACTION 4> INSERT c VALUES ("X") 5> IF (@@ERROR <> 0) GOTO on_error 6> INSERT b VALUES ("X") -- Fails reference 7> IF (@@ERROR <> 0) GOTO on_error 8> COMMIT TRANSACTION 9> RETURN(0) 10> 11> on_error: 12> ROLLBACK TRANSACTION 13> RETURN(1) 14> 15> GO</source>


IF @@ERROR <> 0 GOTO TRAN_ABORT

   <source lang="sql">

3> IF EXISTS (SELECT * FROM sysobjects WHERE name="show_error" 4> AND type="U") 5> DROP TABLE show_error 6> GO 1> 2> CREATE TABLE show_error 3> ( 4> col1 smallint NOT NULL PRIMARY KEY, 5> col2 smallint NOT NULL 6> ) 7> GO 1> 2> BEGIN TRANSACTION 3> INSERT show_error VALUES (1, 1) 4> IF @@ERROR <> 0 GOTO TRAN_ABORT 5> INSERT show_error VALUES (1, 2) 6> if @@ERROR <> 0 GOTO TRAN_ABORT 7> INSERT show_error VALUES (2, 2) 8> if @@ERROR <> 0 GOTO TRAN_ABORT 9> COMMIT TRANSACTION 10> GOTO FINISH 11> 12> TRAN_ABORT: 13> ROLLBACK TRANSACTION 14> 15> FINISH: 16> GO (1 rows affected) Msg 2627, Level 14, State 1, Server J\SQLEXPRESS, Line 5 Violation of PRIMARY KEY constraint "PK__show_error__762D5431". Cannot insert duplicate key in object "dbo.show_error". The statement has been terminated. 1> 2> SELECT * FROM show_error 3> GO col1 col2


------

(0 rows affected) 1> 2> drop table show_error; 3> GO</source>


Using GOTO

   <source lang="sql">

GOTO is used to jump to a label in your Transact-SQL batch. The syntax is: GOTO label label definition: code</source>