ttmind

Main Navigation

ttmind
  • jim-jams
  • Tech
  • Positive
Login

Login

Facebook Google

OR

Remember me Forgot password?

Don't have account? Signup here.

Sort by Categorys

.Net

PHP

Java

JavaScript

Database

Server

Client Side

Tools

Artificial Intelligence

Cloud

Hybrid Development

Event

Smart City

Education

Security

Scrum

Digital Marketing

APP Development

Business

Internet

Simulation

Art

Network

Microservices

Architecture

Technology

Leadership

    Top Articles

  • How Does Social Media Bring People Together?
    TTMind Author
  • How to read appSettings JSON from Class Library in ASP.NET Core
    Anil Shrestha
  • Printing Support In Asp.Net Core
    TTMind Author
  • HOW TO EXTRACT TEXT FROM IMAGE USING JAVASCRIPT (OCR with Tesseract.js)?
    Prakash Pokhrel
  • Images Upload REST API using ASP.NET Core
    Prakash Pokhrel
  • Related Topic

  • Hosting ASP.NET Core Web API on IIS
  • Use of TOP and DISTINCT in SQL SERVER
  • Difference between char, nchar, varchar, and nvarchar in MS SQL Server
  • What's new in SQL Server 2019?
  • Uploading .csv sample table file data to SQL Server
  • Tech
  • About Us
  • Contact Us
  • TechHelp
  • PositiveHelp
  • Jim-Jams Help
  • Terms & Conditions

© Copyright ttmind.com

Main Content

SQL Server Stored Procedures: Part-2

Server SQL Server about 6 years ago || 6/4/2019 || 3.0 K View

  • Hide

Control-of-flow statements:

For SQL Server Stored Procedures: Part-1, Click here.

BEGIN….END Statement:

The BEGIN….END statement contains set of  SQL statement and executes block of SQL statement.

Syntax:

BEGIN
    { SQL Statement || Statement Block}
END

For example:

BEGIN
    SELECT
        First_Name,
        Salary
    FROM
        dbo.EmployeeDb
    WHERE
        Salary> 100000;

    IF @@ROWCOUNT=  0
        PRINT 'No one has greater than 100000';
END

Syntax Description:

  • Block of SQL statement starts with BEGIN and Ends with END.
  • Inside BEGIN….END statement SELECT is used for finding name of employee who has salary more than 100000.
  • IF statement is used to print message if no one have salary more than 100000.

Nesting of BEGIN….END Statement:

BEGIN….END statement also can be nested.

For Example:

BEGIN
    DECLARE @firstName VARCHAR(MAX)
    SELECT TOP 1
        @firstName=First_Name    
    FROM
        dbo.EmployeeDb
    ORDER BY
        Salary DESC  

    IF @@ROWCOUNT <>  0
       BEGIN
              PRINT 'Employee with Highest salary is: '+@firstName
       END
       ELSE
       BEGIN
        PRINT 'No one is selected'
       END
END

IF Statement:

Syntax of IF statement:

IF BooleanExpression  
BEGIN
    { Statement Block || SQL Statement}
END

 For Example:

BEGIN
    DECLARE @firstName VARCHAR(MAX)
    SELECT TOP 1
        @firstName=First_Name    
    FROM
        dbo.EmployeeDb
    ORDER BY
        Salary DESC  
    IF @@ROWCOUNT > 0
       BEGIN
           PRINT 'Employee with Highest salary is: '+@firstName
       END            
END

Output:

IF ELSE Statement:
 

IF BooleanExpession
BEGIN
   {Statement Block|| SQL Statement}
END
ELSE
BEGIN
   {Statement Block|| SQL Statement}
END

Syntax of IF ELSE Statement:

For example:
 

BEGIN
    DECLARE @firstName VARCHAR(MAX)
    SELECT TOP 1
        @firstName=First_Name     
    FROM
        dbo.EmployeeDb
    ORDER BY
        Salary   
    IF @@ROWCOUNT <>  0
       BEGIN
              PRINT 'Employee with Lowest salary is: '+@firstName
       END
       ELSE
       BEGIN
        PRINT 'No one is selected'
       END
END

Output:

Nested IF ELSE Statement:

IF ELSE statement can also be nested.

For example:
 

BEGIN
    DECLARE @A INT = 100,
            @B INT = 500;
    IF (@A > 0)
    BEGIN
        IF (@A < @B)
            PRINT 'A > 0 and A < B';
        ELSE
            PRINT 'A > 0 and A >= B';
    END
END

Output:

WHILE Statement:

Syntax:

WHILE BooleanExpression
      {SQL Statement ||Statement Block}

It takes one expression and check its true or false and executes SQL statement or Statement block.

For Example:

DECLARE @count INT = 0;
WHILE @count <= 5
BEGIN
    PRINT @count;
    SET @count = @count + 1;
END

Syntax description:

@count is used as counter of integer type and its value is set to 0.

Then, WHILE statement is use to check the condition. If the condition is true it print @count and @count value is increased by 1. It stops printing if condition is false.

Output:

BREAK Statement:

BREAK statement is used to exit current loop or iteration.

Syntax:

WHILE BooleanExpression1
BEGIN
   --statement
   IF BooleanExpression2
       BREAK
   --statement
END

For example:

DECLARE @count INT = 0;
WHILE @count <= 5
BEGIN
    SET @count = @count + 1;
    IF @count = 4
        BREAK;
    PRINT @count;
END

Syntax description:

DECLARE is used for declaring @count, WHILE is used for checking condition and SET is used to increase counting.

IF statement checks the condition and if condition is true then BREAK statement is executed which will end the loop.

Output:

CONTINUE Statement:

If CONTINUE statement is encountered current iteration is skipped and loop continues to execute iterations until given condition is false.

Syntax:

WHILE BooleanExpression1
BEGIN
   --statement to be executed
   IF BooleanExpression2
      CONTINUE
   --statement is skipped if condition matches
END

For example:

DECLARE @count INT = 0;
WHILE @count <= 5
BEGIN
    SET @count = @count + 1;
    IF @count = 4
        CONTINUE;
    PRINT @count;
END

Syntax description:

DECLARE is used for declaring @count, WHILE is used for checking condition and SET is used to increase counting.

IF statement checks the condition and if condition is true then CONTINUE statement is executed which skip the next line and keeps on executing statements.

Output:

For SQL Server Stored Procedure: Part-3, Click here.

  • 0
  • 0
  • 0
    • Facebook
    • Twitter
    • Google +
    • LinkedIn

About author

MAT RIX

MAT RIX

Reset Your Password
Enter your email address that you used to register. We'll send you an email with your username and a link to reset your password.

Quick Survey