What are the different types of Named Batches in SQL Server?

Introduction:

In this article i will explain about Named Batches in SQL Server.

Description:

In previous articles i explained about joins, and Batches. Now i will explain about Named Batches in SQL Server.

Batches:

A batch is a group of sql statements. Which are executed as unit. Two types of batches are there. 1). Anonymous Batches and 2). Named Batches

1. Anonymous Batches:

A group of statements which can't be reffered by a name. Then that group/batch is called Anonymous Batches.

2. Named Batches:

A group of statements which can be reffered by a name. Then that group/batch is called Named Batches. Three types of Named Batches are there.

a. Stored Procedures

b. Functions

c. Triggers

Stored Procedure:

It is a set of pre compiled statements which help in reusability.

Syntax:

Create procedure <procedure-name>
(
    <Argument1, Arguments2,...
)
As
Begin

    -- Multiple statements here.

End

Note:

We can alter the existing procedure.

Syntax:

Alter procedure <procedure-name>
(
    <Argument1, Arguments2,...
)
As
Begin

    -- Multiple statements here.

End

Example: 

Add Two numbers using procedure:

Create procedure spAdd
(
    @a int, @b int
)
As
Begin

    -- Declare local variables.
    Declare @c int
    Set @c = @a+@b
    print 'Sum is: '+cast(@c as varchar)

End

How to execute procedure:

There are several ways to execute a procedure.

Execute spAdd 10, 20
(or)
Exec spAdd 10, 20

Output:

Sum is: 30

 Functions and Triggers we will see in next articles.