Basics of stored procedure in SQL Server Management Studio

A Stored Procedure is pre-compiled collection of SQL statements and SQL command logic in stored in database.

The main purpose of stored procedure is to hide direct SQL queries from the code and improve performance of database operations such as SELECT, UPDATE, and DELETE.

Stored Procedures can be cached and used too.

We can create a Stored Procedure using the Create proc statement

SET NOCOUNT ON is a set statement which prevents the message which shows the number of rows affected by T-SQL query statements

NOCOUNT means do not count that is ON.

This means when SET NOCOUNT ON is there then there is no message that will show number of rows affected.

When SET NOCOUNT ON

When SET NOCOUNT OFF

There are two types of Stored Procedures in SQL Server

1. User Defined stored procedures
2. System stored procedures

User Defined Stored Procedures

User defined stored procedures are create database developers or database administrators.

These store procedures contain one or more SQL statement to SELECT, UPDATE, or DELETE records from database tables.

User defined stored procedures can take input parameters and return output parameters.

User defined stored procedures is a mixture of DDL(Data Definition Language) and DML(Data Manipulation Language) commands.

User defines stored procedures are further classified into two types:

• T-SQL Stored Procedures
• CLR Stored Procedures

System Stored Procedures

System Stored Procedures are useful in performing administrative and informational activities in SQL Server. Most commonly used system Stored Procedures are described below.

To Find All Database Object Information

exec sp_help

 Output

To Find Table Information

exec sp_help 'user' 

Output

To find list of all tables in database

EXEC sys.sp_tables  

Output

To get detailed information about tables in database

SELECT * FROM sys.tables  

Output

To get definition of specific procedure “SelectUserDetail” in the master database.

exec sp_helptext SelectAllCustomers

Output

To get the dependent object details.

exec sp_depends Registration  

Output

We can add comments in stored procedure in following ways:

• Single line comment

Use --(two hyphens/ dash) for single line of comment

• Multiline comment

Start with /* …. End with */

                Click on your database and expand “Programmability” and right click on “Stored Procedures” or press CTRL+N to get new query window.

You can write the SELECT query in between BEGIN and END to get select records from the table.

To run stored procedure in SQL server run below commands

• EXEC <store procedure name>
  

Another way to is to right click on Stored Procedure name and select “Execute Stored Procedure”

Parameters are used to pass input values and return output values in store procedures.

• Input parameters: Pass value to a stored procedure

• Output Parameters: Return values from stored procedure

UPDATE query in stored procedure