Stored procedure in sql server (With Example)

: 16232

Last Updated : 07/06/2022

Posted By :- vikas_jk

If you are new to SQL server or have just started coding in SQL server and want to know about stored procedures in SQL server then you have landed on the right website and article, in this article, I have explained about Stored procedure in SQL server with examples.

What is Stored Procedure in SQL server?

Before we begin you must know the basic defination of Stored procedure in SQL server.

A SQL Server stored procedure groups one or more Transact-SQL statements into a logical unit or a reference to a Microsoft .NET Framework common runtime language (CLR) method and is stored as an object in the Database Server, so basically you can say a stored procedure is a group of SQL statements that has been created and stored in the database.

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.

Benefits of using Stored procedures in SQL

Reduced server/client network traffic: A stored procedures will reduce network traffic and increase the performance.
Stronger security : The procedure controls what processes and activities are performed and protects the underlying database objects. This eliminates the requirement to grant permissions at the individual object level and simplifies the security layers
Reuse of Code : The code for any repetitious database operation is the perfect candidate for encapsulation in procedures. This eliminates needless rewrites of the same code, decreases code inconsistency, and allows the code to be accessed and executed by any user or application possessing the necessary permissions
Easier maintenance : When client applications call procedures and keep database operations in the data tier, only the procedures must be updated for any changes in the underlying database. The application tier remains separate and does not have to know how about any changes to database layouts, relationships, or processes.
Improved performance : By default, a procedure compiles the first time it is executed and creates an execution plan that is reused for subsequent executions. Since the query processor does not have to create a new plan, it typically takes less time to process the procedure

Stored procedure Syntax:

USE DatabaseName;  
GO  
CREATE PROCEDURE ProcedureName 
    @InputValue type,   
    @InputValue2 type....   
AS   

    SET NOCOUNT ON;  
     
    //Your SQL query here, like
    Select  FirstName, LastName, Department  
    FROM HumanResources.vEmployeeDepartmentHistory  
    WHERE FirstName = @FirstName AND LastName = @LastName  
GO

Where DatabaseName = name of your database, ProcedureName = name of SP, InputValue = your input parameter value and type = paramter type example nvarchar(50) etc.

Sql server stored procedure example

Now we know the basics of stored procedure, let's check how to create them, we will start from the basic example of it.

To create a procedure, open your local SQL server management studio
In Object Explorer, connect to an instance of Database Engine and then expand that instance.
Expand Databases, expand the AdventureWorks2012 database, and then expand Programmability.
Right-click Stored Procedures, and then click New Stored Procedure, you will see outputlike below:
On the Query menu, click Specify Values for Template Parameters.

You can now Specify Values for Template Parameters inside the dialog box, enter the following values for the parameters shown.

Parameter	Value
Author	Your name
Create Date	Today's date
Description	ForTesting.
Procedure_name	HumanResources.TestGetEmployees
@Param1	@LastName
@Datatype_For_Param1	nvarchar(50)
Default_Value_For_Param1	NULL
@Param2	@FirstName
@Datatype_For_Param2	nvarchar(50)
Default_Value_For_Param2	NULL

Once done, click OK

Now,In the Query Editor, replace the SELECT statement with the following statement:

SELECT FirstName, LastName, Department  
FROM HumanResources.vEmployeeDepartmentHistory  
WHERE FirstName = @FirstName 
      AND LastName = @LastName  
      AND EndDate IS NULL;

To test the syntax, on the Query menu, click Parse (Ctrl+F5) , if you get any error, we need to resolve it.
Now, stored procedure is still not created/saved, so to create the procedure, from the Query menu, click Execute. The procedure is created as an object in the database.
To see the procedure listed in Object Explorer, right-click Stored Procedures and select Refresh.
To run the procedure, in Object Explorer, right-click the stored procedure name HumanResources.uspGetEmployeesTest and select Execute Stored Procedure.
In the Execute Procedure window, enter Margheim as the value for the parameter @LastName and enter the value Diane as the value for the parameter @FirstName.
output:

Create stored procedure using T-SQL

We have seend above how we can create stored procedure using GUI of SQl server management studio, but if you want to create stored procedure using T-SQL query, here is the steps:

From the File menu, click New Query.

Use the following query example into the query window and click Execute. This example creates the same stored procedure as above using a different procedure name.

USE AdventureWorks2012;  
GO  
CREATE PROCEDURE HumanResources.uspGetEmployeesTestNew   
    @LastName nvarchar(50),   
    @FirstName nvarchar(50)   
AS   

    SET NOCOUNT ON;  
    SELECT FirstName, LastName, Department  
    FROM HumanResources.vEmployeeDepartmentHistory  
    WHERE FirstName = @FirstName AND LastName = @LastName  
    AND EndDate IS NULL;  
GO

stored-procedure-in-sql-server-example-using-t-sql-min.png

To run the procedure, copy and paste the following example into a new query window and click Execute. Notice that different methods of specifying the parameter values are shown.

EXECUTE HumanResources.uspGetEmployeesTest2 N'Ackerman', N'Pilar';  
-- Or  
EXEC HumanResources.uspGetEmployeesTest2 @LastName = N'Ackerman', @FirstName = N'Pilar';  
GO  
-- Or  
EXECUTE HumanResources.uspGetEmployeesTest2 @FirstName = N'Pilar', @LastName = N'Ackerman';  
GO?

Output:

Deleting a SQL server stored procedure

To delete a stored procedure, you use the DROP PROCEDURE or DROP PROC statement:

DROP PROCEDURE <stored procedure name>;  
GO

DROP PROC <stored procedure name>;

where <stored procedure name> is the name of the stored procedure that you want to delete.

Example:

DROP PROCEDURE HumanResources.uspGetEmployeesTest2;  
GO

after deleting you also need to remove references to the procedure from any dependent objects and scripts.

Calling Stored procedure using C#

Now, once we have stored procedure ready, we will have to call it using C# code, and it can be called as shown below

private void CallStoredProcedure() {
 var ConnectionString= "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"; //your connection  string here

  using (SqlConnection con = new SqlConnection(ConnectionString)) {
    using (SqlCommand cmd = new SqlCommand("StoredProcedureName", con)) {

      cmd.CommandType = CommandType.StoredProcedure;

      //add parameters of SP
      cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = "First Name";
      cmd.Parameters.Add("@LastName", SqlDbType.VarChar).Value = "Last Name";

      con.Open();
      cmd.ExecuteNonQuery();
    }
  }
}

If stored procedure returns values, you can have C# code as below

private void CallStoredProcedure() {
 var ConnectionString= "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI"; //your connection  string here

  using (SqlConnection con = new SqlConnection(ConnectionString)) {
    using (SqlCommand cmd = new SqlCommand("StoredProcedureName", con)) {

       cmd.Parameters.AddWithValue("FirstName", "First Name");
       cmd.Parameters.AddWithValue("LastName", "last Name");

       var returnParameter = cmd.Parameters.Add("@ReturnVal", SqlDbType.Int);
       returnParameter.Direction = ParameterDirection.ReturnValue;

       conn.Open();
       cmd.ExecuteNonQuery();
       //return value
       var result = returnParameter.Value;
    }
  }
}

Calling Stored Procedure in ASP.NET Core using EF Core

You can simply call Stored procedure in .NET Core using EF, by using RAW SQL Command

Example, calling SP with parameters

var productCategory= "Electronics";

    var product = context.Products
        .FromSql("EXECUTE dbo.GetProductByCategory {0}", productCategory)
        .ToList();

You can also create a custom function

public async Task ExecuteStoredProc()
{
	DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();

	cmd.CommandText = "dbo.sp_DoSomething";
	cmd.CommandType = CommandType.StoredProcedure;

	cmd.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar) { Value = "Steve" });
	cmd.Parameters.Add(new SqlParameter("@lastName", SqlDbType.VarChar) { Value = "Smith" });

	cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.BigInt) { Direction = ParameterDirection.Output });

	if (cmd.Connection.State != ConnectionState.Open)
	{
		cmd.Connection.Open();
	}

	await cmd.ExecuteNonQueryAsync();

	long id = (long)cmd.Parameters["@Id"].Value;
}

Where '_Context' = Database Context.

Without Parameters, it would be simply

      var product = context.Products
        .FromSql("EXECUTE dbo.GetProducts")
        .ToList();

That's it.

You may also like to read:

How to open sql server configuration manager in windows 10?

How to back up SQL Server database ? (Various Ways explained)

How to check sql server version? (Various ways explained)

Import csv into SQL server (with query OR without query using SSMS)

Difference between SQL and NoSQL (SQL vs NoSQL)

Import data from Excel to SQL Server

Remove all whitespace from string in SQL Server