Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

BREAK OR CONTINUE

BREAK:

The BREAK statement exits the innermost WHILE loop, a program might execute a BREAK statement if, for example, there are no other rows to process. BREAK keyword will exit the stop the while loop and control is moved to next statement after the while loop.

Example:

DECLARE @empCount INT
 
WHILE @empCount<(Select COUNT(Id) FROM Employee)
BEGIN
Set @empCount=@empCount+1
   UPDATE Employee
      SET EmpType = 1 WHERE ManagerId is not null and ID=@empCount
 
   IF (Select COUNT(Id) FROM Employee where ManagerId is not null)=0
      BREAK
   ELSE
      CONTINUE
END
PRINT 'Updated Employee type';
 

CONTINUE:

CONTINUE statement restarts a WHILE loop, any statements after the CONTINUE keyword are ignored. CONTINUE is frequently, but not always, opened by an IF test.

CONTINUE keyword skips all the statement after its execution and control is sent to first statement of while loop. Continue keyword mostly used to continue loop.

Example:

---return list of employee details and count for given manager 
 
DECLARE @empCount INT
 
SET @empCount=1
 
WHILE @empCount<=4
BEGIN
Set @empCount=@empCount+1
PRINT @empCount;
 IF @empCount=4
 begin
      PRINT 'inside loop';
      BREAK
  end
 ELSE
      CONTINUE
END
 

GOTO statement

 GOTO statement

The GOTO statement causes the execution of a Transact-SQL batch to jump to a label. None of the statements between the GOTO statement and the label are executed.

The label that is the target of a GOTO identifies only the target of the jump.

Note: GOTO statements and labels can be used anywhere within a procedure, batch, or statement block. GOTO statements can be nested.

Label: Is the point after which processing starts if a GOTO is targeted to that label. Labels must follow the rules for identifiers. A label can be used as a commenting method whether GOTO is used

Syntax

Define the label:
Label: 
Alter the execution:
GOTO label

Example:

 
---return list of employee details and count for given manager 
DECLARE @managerId INT
SET @managerId=2
 
DECLARE @empCount INT
SELECT @empCount=COUNT(ID) FROM Employee WHERE ManagerId=@managerId
 
IF @empCount<> 0 and @managerId <>0
BEGIN
GOTO Success_Statment;
END
ELSE
BEGIN
GOTO Generic_Message;
END
 
Success_Statment:
SELECT EmployeeName,Country,PrimaryLanguage,CompanyPlant FROM Employee WHERE ManagerId=@managerId
SELECT @empCount AS WorkingEmployees
 
Generic_Message:
PRINT 'Employee not found’

Begin..End

Begin..End:

Series of SQL statements, So that group of sql stamen executed as group, real example use is if you want to put all DML statement (insert,update,delete) when conditions satisfies I want to roll-back transaction

To define a statement block (batch), use the control-of-flow language keywords BEGIN and END. Although all Transact-SQL statements are valid within a BEGIN...END block, certain Transact-SQL statements should not be grouped together within the same batch (statement block).

Syntax:

BEGIN
     {
    sql_statement | statement_block
     }
END

 

Example:

---return list of employee details and count for given manager 
 
DECLARE @managerId INT
SET @managerId=2
 
DECLARE @empCount INT
SELECT @empCount=COUNT(ID) FROM Employee WHERE ManagerId=@managerId
 
IF @empCount<> 0 and @managerId <>0
 
BEGIN
 
SELECT EmployeeName,Country,PrimaryLanguage,CompanyPlant FROM Employee WHERE ManagerId=@managerId
 
SELECT @empCount AS WorkingEmployees
 
END

              

Result:


Note: Begin…End statement can be nested