<img alt="" src="https://secure.leadforensics.com/150446.png " style="display:none;">

Basics of stored procedure in SQL Server Management Studio

Aarti Shinde Nov 17, 2019

SQL SQL Server Management Studio

Definition of Stored Procedure

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

01

Use of SET NOCOUNTON in Stored Procedure

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

on

When SET NOCOUNT OFF

off

Types of Stored Procedure

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

output

To Find Table Information

exec sp_help 'user' 

Output

us-1

To find list of all tables in database

EXEC sys.sp_tables  

Output

output-3

To get detailed information about tables in database

SELECT * FROM sys.tables  

Output

u-5

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

exec sp_helptext SelectAllCustomers

Output

output3

To get the dependent object details.

exec sp_depends Registration  

Output

output-3

Comments in Stored Procedures

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 */

Create SELECT Store Procedure

                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.

               c1

Execute Stored Procedure in SQL Server

To run stored procedure in SQL server run below commands

  • EXEC <store procedure name>
    c-2

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

c-4

Parameters in Stored Procedures

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

  • Input parameters: Pass value to a stored procedure

i1

  • Output Parameters: Return values from stored procedure

i2

INSERT query in stored procedure

i3

UPDATE query in stored procedure

i5

DELETE query in stored procedure

i6

e-Zest is a leading digital innovation partner for enterprises and technology companies that utilizes emerging technologies for creating engaging customers experiences. Being a customer-focused and technology-driven company, it always helps clients in crafting holistic business value for their software development efforts. It offers software development and consulting services for cloud computing, enterprise mobility, big data and analytics, user experience and digital commerce.