---
title: "Stored Procedure in SQL Server"  
description: "Stored procedures are a powerful part of SQL Server. They can assist programmers and administrators greatly in working with the database configuration"  
author: "AVADHESH PATEL"  
published: 2012-08-25  
updated: 2014-09-18  
canonical: https://www.mindstick.com/blog/344/stored-procedure-in-sql-server  
category: "database"  
tags: ["database"]  
reading_time: 3 minutes  

---

# Stored Procedure in SQL Server

**[Stored procedures](https://www.mindstick.com/forum/540/using-stored-procedures-with-entity-framework-in-an-asp-dot-net-application)** are a powerful part of [SQL Server](https://www.mindstick.com/articles/12999/what-is-table-valued-function-in-sql-server). They can assist programmers and administrators greatly in working with the database [configuration](https://www.mindstick.com/articles/13112/setting-up-the-perfect-configuration-for-your-work-computer) and its data.\

A [stored procedure](https://www.mindstick.com/articles/803/using-stored-procedure-in-asp-dot-net) is a precompiled group of Transact-SQL statements, and is saved to the database (under the "Stored Procedures" node). Programmers and administrators can [execute stored](https://www.mindstick.com/interview/2542/how-can-we-execute-stored-procedures-and-functions) procedures either from the SQL Server [Management Studio](https://www.mindstick.com/forum/159668/what-is-the-difference-between-sql-server-and-sql-management-studio) or from within an [application as](https://answers.mindstick.com/qa/41069/how-did-missouri-s-application-as-a-slave-state-in-1819-challenge-u-s-law) required.

##### Types of Stored Procedure

Basically there are two types of stored procedure first is System Stored Procedure and second one is User Define Stored procedure.

##### System Stored Procedure:

In SQL Server, many administrative and informational activities can be performed by using system stored procedures. Every time we [add or modify](https://www.mindstick.com/forum/160256/how-to-add-or-modify-a-model-in-entity-framework-core-and-generate-a-migration-for-those-changes) a table, make a backup plan, or perform any other administrative function from within Enterprise Manager, we actually call a stored procedure specifically written to complete the desired action. These stored procedures are [known as](https://answers.mindstick.com/qa/35703/ricky-ponting-is-also-known-as-what) system stored procedures.

System stored procedure is those stored procedure which is available by the SQL server such as for renaming table name there is one stored procedure namely SP_RENAME , so such types of stored procedure are called system stored procedure.

##### User Define Stored Procedure:

User Define stored procedure are those procedure which are developed by user for obtaining particular goal of task. The user are restrict to create stored procedure in master database with prefix name sp of stored procedure, because sp prefix name is used for system stored procedure in master database.

##### Creating a User Define Stored Procedure

You create stored procedures in the SQL [Server Management](https://www.mindstick.com/interview/33968/how-to-clear-the-cache-in-sql-server-management-studio-ssms) Studio (SSMS) using the CREATE PROCEDURE statement, followed by the code that makes up the stored procedure.

##### Syntax for Stored Procedure

```
--SYNTAX DEMONSTRATION OF CREATING USER DEFINE STORED PROCEDURE CREATE PROCEDURE <PROCEDURE_NAME>(  -- PARAMETER OF STORED PROCEDURE )ASBEGIN--PROCEDURE BODY --WRITE SELECT, INSERT, UPDATE, DELETE ETC COMMAND HEREENDGO
```

##### Example: CREATING STORED PROCEDURE

```
SET ANSI_NULLS ON -- that compares a value with a null return a 0(zero)GOSET QUOTED_IDENTIFIER ON -- double quotation mark is used as part of the SQL Server identifier(object name)GO -- creating procedure CREATE PROCEDURE TEST_PROCEDURE       -- Add the parameters for the stored procedure here      @ID INTASBEGIN    -- SELECT statements for procedure here      SELECT * FROM info where id = @IDEND
```

##### Execution Stored Procedure

```
EXEC TEST_PROCEDURE '1' -- calling stored procedure with passing parameter
```

##### Drop Stored Procedure

```
DROP PROC TEST_PROCEDURE -- we can use PROC in place of PROCEDURE keywordAlter Stored Procedure-- Modify the Procedure ALTER PROC TEST_PROCEDURE      -- Add the parameters for the stored procedure here      @NAME VARCHAR(50)ASBEGIN    -- SELECT statements for procedure here      SELECT * FROM info where [name] = @NAMEEND
```

For more information you can prefer this link…

http://www.mindstick.com/Beginner/79BE36EC-D4E6-4EF5-AE5D-C65FD7EDE2B9/SQL%20Server/Stored%20Procedure%20in%20SQL/

---

Original Source: https://www.mindstick.com/blog/344/stored-procedure-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.