Stored Procedure In SQL Server

Definition

A stored procedure is a programming code that runs in the address space of a Database Management System (DBMS). The term stored procedure refers to a procedure we write with SQL statements. It is a database object executed in Teradata database. Typically a stored procedure consists of a procedure name, input and output parameters, and a procedure body.

Index

1. How to create a Stored Procedure
2. How to modify a Stored Procedure
3. How to delete a Stored Procedure
4. Grant Permissions on a Stored Procedure
5. How to return data from a Procedure
6. How to get Stored Procedure’s data from a C# application
7. Dependencies of a Stored Procedure

Stored Procedure Benefits

A stored procedure provides control and condition handling statements, in addition to multiple input and output parameters and local variables, that make SQL a computationally complete programming language. Applications based on stored procedure provide the following benefits over equivalent embedded SQL applications:

1. Better performance because of greatly reduced network traffic between the client and server.
2. Better application maintenance because business rules are encapsulated enforced on the server.
3. Better transaction control.
4. Better application security by restricting user access to procedures rather than requiring them to access data table directly.

How to create a Stored Procedure

Here, I will create a simple stored procedure which will retrieve data based on the given parameter value.

In SQL Management Studio expand your database under which you want to create the procedure. There will be a directory name “Programmability”.



Expand the mentioned directory and create a new stored procedure right clicking on the “Stored Procedure.” Select the “Specify Values for Template Parameters” option from “Query” menu.



In the opened dialog box specify the values as in the following:



Now the template will be like the following:



We have to replace the SQL SELECT statement with our own query. I have used a simple SELECT query here.

SELECT*FROMdbo.testWHEREID=@IDANDEmail=@Email;

We can test our query syntax from “Parse” option under “Query” menu. To create this procedure click the “Execute” option. After refreshing the “Stored Procedure” directory the procedure will be visible here with the given name.

So we have successfully created our first stored procedure using SQL Management Studio. I’ll discuss the other topics sequentially in my next article.