How to Use 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.

1. To create a procedure, open your local SQL server management studio
2. In Object Explorer, connect to an instance of Database Engine and then expand that instance.
3. Expand Databases, expand the AdventureWorks2012 database, and then expand Programmability.
4. Right-click Stored Procedures, and then click New Stored Procedure, you will see outputlike below:

5. On the Query menu, click Specify Values for Template Parameters.

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

7. Once done, click OK

8. 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;  

9. To test the syntax, on the Query menu, click Parse (Ctrl+F5) , if you get any error, we need to resolve it.

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

11. To see the procedure listed in Object Explorer, right-click Stored Procedures and select Refresh.

12. To run the procedure, in Object Explorer, right-click the stored procedure name HumanResources.uspGetEmployeesTest and select Execute Stored Procedure.

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

14. 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:

1. From the File menu, click New Query.
2. 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  

3. 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?

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

OR

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

Conclusion

I hope article above help to know further about stored procedure in SQL server.