What is Stored Procedures

SQL Server Stored Procedures

A SQL Server stored procedure groups one or more Transact-SQL statements into a logical unit and is stored as an object in the Database Server. When a stored procedure is called at the first time, SQL Server creates an execution plan and stores it in the plan cache. In the subsequent executions of the stored procedure, SQL Server reuses the plan so that the stored procedure can execute very fast with reliable performance.

Benefits of using stored procedures

A stored procedure provides an important layer of security between the user interface and the database. It supports security through data access controls because end users may enter or change data, but do not write procedures. A stored procedure preserves data integrity because information is entered in a consistent manner. It improves productivity because statements in a stored procedure only must be written once.

The main purpose of stored procedures to hide direct SQL queries from the code and improve performance of database operations such as select, update, and delete data.

Why do we use SET NOCOUNT ON in a stored procedure?

While we set SET NOCOUNT ON it means there is no messages which shows the number of rows affected.

NOCOUNT means do not count that is ON.

Now you will come to know what happened when SET NOCOUNT OFF.

Types of stored procedures

There are two types of stored procedures available in SQL Server:

1. User defined stored procedures

2. System stored procedures

User defined stored procedures

User defined stored procedures are created by database developers or database administrators. These SPs contains one more SQL statements to select, update, or delete records from database tables. User defined stored procedure can take input parameters and return output parameters. User defined stored procedure is mixture of DDL (Data Definition Language) and DML (Data Manipulation Language ) commands.

User defined SPs are further classified into two types:

T-SQL stored procedures: T-SQL (Transact SQL) SPs receive and returns parameters. These SPs process the Insert, Update and Delete queries with or without parameters and return data of rows as output. This is one of the most common ways to write SPs in SQL Server.

CLR stored procedures: CLR (Common Language Runtime) SPs are written in a CLR based programming language such as C# or VB.NET and are executed by the .NET Framework.

System stored procedures

System stored procedyres are created and executed by SQL Server for the server administrative activities. Developers usually don't interfere with system SPs.

What is the naming convention for stored procedures?

We must follow standard naming conventions which may also depend on your project and coding policies.

For user defined stored procedure naming conventions, my suggestions are to add one of the following prefixes to your SP names.

1. sp

2. stp

3. stp_

4. udstp

5. udstp_

Naming conventions are just to identify objects. By adding these prefixes in the name, we can clearly identify that this object is a stored procedure.

Syntax

Following is the basic syntax of Stored procedure creation.

Create procedure <procedure_Name>

As

Begin

<SQL Statement>

End

Go