SQL Server Control Of Flow

Level : Beginner

Mentor: Shailendra Chauhan

Duration : 00:02:00

BEGIN...END

The BEGIN...END block is used in SQL Server to group multiple statements together. It is often used with control-of-flow statements like IF...ELSE and WHILE to create a logical block of code.

Example

BEGIN
  -- SQL statements here
  SELECT * FROM Employees;
END;

RETURN

RETURN is used to exit a stored procedure and return a value or result set to the calling program or query.

Example

CREATE PROCEDURE GetEmployeeCount
AS
BEGIN
  DECLARE @Count INT;
  SET @Count = (SELECT COUNT(*) FROM Employees);
  RETURN @Count;
END;

BREAK

BREAK is used within a loop to exit the loop prematurely, without completing all iterations.

Example

DECLARE @i INT = 1;
WHILE @i <= 10
BEGIN
  IF @i = 5
    BREAK;
  PRINT 'Value: ' + CAST(@i AS NVARCHAR(2));
  SET @i = @i + 1;
END;

THROW

THROW is used to raise an exception or error within a TRY...CATCH block, allowing for more controlled error handling.

Example

BEGIN TRY
  -- Some code that may raise an error
  RAISEERROR('This is an error message.', 16, 1);
END TRY
BEGIN CATCH
  -- Handle the error here
  THROW;
END CATCH;

CONTINUE

CONTINUE is used within a loop to skip the current iteration and proceed to the next one.

Example

DECLARE @i INT = 1;
WHILE @i <= 5
BEGIN
  IF @i = 3
  BEGIN
    SET @i = @i + 1;
    CONTINUE;
  END
  PRINT 'Value: ' + CAST(@i AS NVARCHAR(2));
  SET @i = @i + 1;
END;

TRY...CATCH

TRY...CATCH is used for structured error handling. It allows you to enclose a block of code in a TRY block and catch and handle exceptions in a CATCH block.

Example

BEGIN TRY
  -- Code that may cause an error
  SELECT 1 / 0;
END TRY
BEGIN CATCH
  -- Handle the error here
  PRINT 'An error occurred: ' + ERROR_MESSAGE();
END CATCH;

GOTO label

GOTO is used to transfer control to a specified label within a batch or stored procedure. It should be used sparingly as it can make code less readable.

Example

DECLARE @i INT = 1;
LOOP_START:
PRINT 'Value: ' + CAST(@i AS NVARCHAR(2));
SET @i = @i + 1;
IF @i <= 5
  GOTO LOOP_START;

WAITFOR

WAITFOR is used to introduce a delay in SQL Server, allowing you to pause execution for a specified period or until a specific time is reached.

Example

WAITFOR DELAY '00:00:05'; -- Wait for 5 seconds
PRINT 'Delay completed.';

IF...ELSE

IF...ELSE is used for conditional branching. It allows you to execute different sets of statements based on a specified condition.

Example

DECLARE @Age INT = 25;
IF @Age >= 18
  PRINT 'You are an adult.';
ELSE
  PRINT 'You are a minor.';

WHILE

WHILE is used to create a loop that executes a block of code repeatedly as long as a specified condition is true.

Example

DECLARE @i INT = 1;
WHILE @i <= 5
BEGIN
  PRINT 'Value: ' + CAST(@i AS NVARCHAR(2));
  SET @i = @i + 1;
END;

Self-paced Membership

Upto 60% OFF

Know More

Still have some questions? Let's discuss.