T-SQL Tutorial

BEGIN...END


In SQL Server, the BEGIN END statements used together form a block.
With the BEGIN statement, the creation of a block begins, it basically represents the starting point of the SQL block. With the END statement, the SQL block ends, practically END closes BEGIN, it represents the end point of the SQL block.
Inside the SQL BEGIN END block you can execute SQL or T-SQL statements such as: SELECT, INSERT, UPDATE, DELETE, execution of functions and procedures.


Syntax

BEGIN
{ sql_statements | transact_sql_statement }
END


Example of BEGIN...END with SQL statements

The first example contains the basic BEGIN...END statement. The body of the block contains the SQL commands: INSERT, UPDATE, DELETE.

USE model
GO
BEGIN
INSERT INTO students(id, name, birthday, city)
VALUES (6, 'Smith', '1991-10-07', 'Boston');
UPDATE students SET name='Tom' WHERE id=3;
DELETE FROM students WHERE name ='John';
END;
GO


Example of BEGIN...END with WHILE

The second example uses the WHILE statement with the BEGIN...END block inside it. In the body of the BEGIN...END block, the @count variable is incremented by 1 at each WHILE iteration. When @count will have the value 3, the BREAK statement is used to exit the BEGIN...END block and the WHILE iterations end.

USE model
GO
DECLARE @count int
SET @count = 1
WHILE @count < 5
BEGIN
PRINT 'Test'
SET @count = @count + 1
IF @count = 3
BREAK
END;
GO





Example of BEGIN...END with TRY...CATCH

The following example uses BEGIN...END together with TRY...CATCH. In the body of the BEGIN TRY...END TRY block, the error is generated, then the error is handled in the BEGIN CATCH...END CATCH block.

USE model
GO
DECLARE @ErrorMessage varchar(2000);
BEGIN TRY
-- Generate Divide by zero error encountered.
SELECT 5/0;
END TRY
BEGIN CATCH
-- Catch error message.
SELECT @ErrorMessage=ERROR_MESSAGE();
PRINT @ErrorMessage
END CATCH;
GO