Skip to main content

Stored Procedure Explained


SQL Stored Procedure Explained


A SQL Server stored procedure, groups one or more Transact-SQL statements into a logical unit and is stored as an object in the Database Server. When a stored procedure is called at the first time, SQL Server creates an execution plan and stores it in the plan cache. In the subsequent executions of the stored procedure, SQL Server reuses the plan so that the stored procedure can execute very fast with reliable performance as explained in SQL Server Tutorial site


This post introduces you to the stored procedures and shows you how to develop flexible stored procedures to optimize your database access whether with C# or SQL Server Management Studio.

Connect to SQL Server through Microsoft SQL Server Management Studio as shown below




Stored Procedure Syntax (Create)


CREATE PROCEDURE procedure_name
AS
BEGIN
sql_statement
END

EXC procedure_name

Explanation of the syntax


CREATE is a keyword in Transact sql for creating new stored procedure.
PROCEDURE is also a keyword in Transact SQL that signifies creating a stored procedure. We can also use PROC keyword instead of the long name.
AS keyword is to specify the transact sql statements or command logics.
BEGIN and END is use to wrap the t-sql statements if we have more than one, but can be omitted if it’s only one statement.

We are going to be using the csharpnaija_employees database and employees, departments tables created from previous post.

Employees table




Departments table




 In this post, we will learn how to manage stored procedures in SQL Server including creating, executing, modifying, and deleting stored procedures.

Creating a Stored Procedure

For instance, if we want to get records from employees table, we use the below code
SELECT * FROM employees                      
But if we are going to be using this statement in several places, we create a stored procedure that encapsulate the above statement so as to reuse the code.
To create a stored procedure that wraps this query, you use the CREATE PROCEDURE statement as follows
We can graphically create this stored procedure in MSSMS as outlined below
Expand csharpnaija_employees database from object explorer as below



Expand the programmability folder under the csharpnaija_employees as shown below

Right click on Stored Procedures folder, Select New and click on Stored Procedure as shown below

From the new stored procedure template automatically created, change the procedure name, parameters and the statement as shown below

Change the template to look like the below
-- =============================================

-- Author:           <Musa Sule>
-- Create date: <20/01/2020>
-- Description:      <Get All Employees with their respective departments>
-- =============================================
CREATE PROCEDURE GetAllEmployees
AS
BEGIN
       SET NOCOUNT ON;
       SELECT e.FirrstName,e.MiddleName,e.LastName,e.PhoneNumber,e.Address, d.DeptName
       FROM employees e
       INNER JOIN departments d
       ON e.DepartmentId=d.Id
END
GO

Click on Execute  to create and save the procedure for later use
If everything is ok, Commands completed successfully as shown in the Messages window below
Now expand the stored procedures folder to see the newly created procedure as shown below

To execute the created procedure, we call EXECUTE sp_name;

EXEC GetAllEmployees


Modifying a stored procedure

To modify an existing stored procedure, you use the ALTER PROCEDURE statement.
First, open the stored procedures to view its contents by right-clicking the stored procedure name you want to modify and select Modify as shown below

The stored procedure to be modified will open in query window as shown below


Change anything you want to change from the T-SQL statements and click on Execute as shown below


The result of the modified stored procedure is as shown below



Deleting a stored procedure


To delete a stored procedure, we will use the DROP PROCEDURE or DROP PROC statement in the query window.

Syntax


DROP PROCEDURE sp_name; or DROP PROC sp_name;

We can also use the Graphical User Interface in the Microsoft SQL Server Management Studio
Right click on the stored procedure you want to delete as shown below   

A Delete Object window will be displayed, click on OK to Delete the stored Procedure as shown below

The stored procedure will deleted and removed from the Stored Procedure folder.

Labels:
Location: Abuja, Nigeria

Comments

Post a Comment

Popular posts from this blog

The String.Join Method in C# Explained

The String.Join Method in C#   The string.Join concatenates the elements of a specified array or the members of a collection, using the specified separator between each element or member. Overloads of string.Join Method Description Join(Char, Object[]) Concatenates the string representations of an array of objects, using the specified separator between each member. Join(Char, String[]) Concatenates an array of strings, using the specified separator between each member. Join(String, IEnumerable<String>) Concatenates the members of a constructed IEnumerable<T> collection of type String, using the specified separator between each member. Join(String, Object[]) Concatenates the elements of an object array, using the specified separator between each element. Join(String, String[]) Concatenates all the elements of a string array, usi...
Post a Comment
Read more

Models in ASP.NET MVC

Models in ASP.NET MVC Explained A model is a class that contains the business logic of your application. It also used for accessing data from the database. The model class does not handle directly input from the browser. In MVC, it is the Controller that handle input from the browser directly and process the request by receiving data from the model and pass it back to view as response. It does not contain any HTML code either. It is a best practice but not mandatory for developers to not have any communications with the view directly, models should only contain a POCO ( Plain Old CLR Objects ) classes. All processing logic and communication with the view should be handled by another layer called Viewmodels. Models are also refers as objects that are used to implement conceptual logic for the application. A controller interacts with the model, access the data, perform the logic and pass that data to the view. Note that it is not mandatory, but it is a good programming...
Post a Comment
Read more

Classes in C# Explained

C# Class Explained A class is nothing but an encapsulation of properties and methods that are used to represent a real-time entity, as explained by Guru99 . For instance, if you want to work with Guest’s data as in our previous DataDriven Web application . The properties of the Guest would be the Id, GuestName, Address, Phone number etc of the Guest. The methods would include the entry and modification of Guest data. All of these operations can be represented as a class in C# as shown below. using System; namespace CsharpnaijaClassTutorial {     public class Guest     {         public int Id { get ; set ; }         public string GuestName { get ; set ; }         public string Address { get ; set ; }         public string WhomToSee { get ; set ; }     ...
1 comment
Read more