---
title: Basics of stored procedure in SQL Server Management Studio
description: Stored procedues in SQL allows us to create SQL queries to be stored and executed on the server. This blog explains how to manage stored procedures in SQL Server.
---

[e-Zest members share technology ideas to foster digital transformation.](https://blog.e-zest.com)

# [Basics of stored procedure in SQL Server Management Studio](https://blog.e-zest.com/basics-of-stored-procedure-in-sql-server-management-studio)

 Written by [Aarti Shinde](https://blog.e-zest.com/author/aarti-shinde) | Nov 17, 2019, 5:59:00 AM

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

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

When SET NOCOUNT 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**

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

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

               

**Execute Stored Procedure in SQL Server**

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

- **Output** Parameters: Return values from stored procedure

**INSERT query in stored procedure**

**UPDATE query in stored procedure**

**DELETE query in stored procedure**

[View full post](https://blog.e-zest.com/basics-of-stored-procedure-in-sql-server-management-studio)

```json
{
  "@context" : "http://schema.org",
  "@type" : "BlogPosting",
  "author" : {
    "@type" : "Person",
    "name" : "Aarti Shinde"
  },
  "dateModified" : "2019-11-22T08:53:17.350Z",
  "datePublished" : "2019-11-17T05:59:00Z",
  "headline" : "Basics of stored procedure in SQL Server Management Studio",
  "image" : {
    "@type" : "ImageObject",
    "height" : 1334,
    "url" : "https://cdn2.hubspot.net/hubfs/744339/Basics%20of%20Stored%20Procedure%20in%20SQL%20Server%20Management%20Studio-191118.png",
    "width" : 2560
  },
  "mainEntityOfPage" : "https://blog.e-zest.com/basics-of-stored-procedure-in-sql-server-management-studio",
  "publisher" : {
    "@type" : "Organization",
    "logo" : {
      "@type" : "ImageObject",
      "height" : 60,
      "url" : "/hs/hsstatic/content_shared_assets/static-1.4092/img/default-amp-logo.png",
      "width" : 60
    },
    "name" : "Blogs"
  }
}
```