Create and Execute Dynamic Stored Procedure

In this article, I am going to explain how to create a dynamic stored procedure in an SQL server and also explain how you can pass a dynamic where clause in an SQL query. In my previous article, I explained how to create and execute parameterized stored procedures From another stored procedure, and here I will also show you how to execute a dynamic stored procedure in an SQL server for example.

While we working with any data-driven application sometimes it becomes necessary to get better and faster output and fetch data from the database much faster. 

Sometimes as per our requirement, we need to get required data/information from the table using the where clause, but in some cases, we didn't have any fixed criteria or have too many columns in the where clause so at that time we required dynamic where clause where we can pass where clause dynamically as per our requirement in SQL server.

Requirement

Today, the same thing happens to me actually as per my client's requirement I have created a report for the month and year-wise stock exchange and I have multiple tables and views with too much data on tables and from that, I required some columns but I didn't and any fixed criteria to pass where clause and filter records from a table and get filtered records from tables only, the user may be search records with year, month, stock date, entry date, RGC date, RGC department, section type and etc. To archive this kind of requirement I have written a dynamic stored procedure where I will pass dynamic criteria in where clause to filter data from a table.

1. Create Dynamic Stored Procedure in SQL Server.
2. Create Dynamic Where Criteria and Store in String Variable.
3. Filter and fetch Record From Tables, passing string a variable as criteria at where clause of the select statement.
4. Execute Dynamic Stored Procedure in SQL Server.

Implementation

Before starting our actual implementation we need a database, tables, and some dummy records for demonstration, So first we will create a database, and tables and also insert some dummy records in the created table.

Create Database

CREATE DATABASE db_Employee

Create Table

CREATE TABLE [dbo].[Employee_Detail]
(
 [EmpId] INT NOT NULL PRIMARY KEY, 
    [FirstName] VARCHAR(50) NULL, 
    [LastName] VARCHAR(50) NULL, 
    [Department] VARCHAR(50) NULL, 
    [Designation] VARCHAR(50) NULL, 
    [Country] VARCHAR(50) NULL
)

Insert Records in Table

INSERT INTO Employee_Detail (EmpId, FirstName, LastName, Department, Designation, Country) VALUES
       (1,'Nikunj','Satasiya','IT','Software Engineer','India'),
       (2,'Hiren','Dobariya','IT','Web Devloper','India'),
       (3,'Krishna','Patel','IT','Web Devloper','UK'),
       (4,'Vivek','Ghadiya','IT','Software Engineer','India'),
       (5,'Pratik','Pansuriya','IT','Software Engineer','India'),
       (6,'Sneha','Patel','IT','Web Devloper','India')

Now, We have a table with few records and are ready to start writing a dynamic stored procedure in the SQL server.

SQL Server Dynamic Stored Procedure

CREATE PROCEDURE Get_Employee_Detail
-- Dynamic  where clause Criteria
@SearchCriteria  AS VARCHAR(800) ='' --'Employee_Detail.EmpId IN(1,3,5) AND Employee_Detail.Country <>''UK'''
AS
BEGIN
DECLARE @StringQuery AS VARCHAR(800) = ' 
SELECT
EmpId, FirstName, LastName, Department, Designation, Country
FROM Employee_Detail WITH(NOLOCK)
'
IF @SearchCriteria = ''
 BEGIN
  -- Execute Sql Statement
  EXECUTE (@StringQuery )
 END
 ELSE
 BEGIN
  -- Execute Sql Statement with dynamic where clause
  EXECUTE (@StringQuery + ' WHERE '  + @SearchCriteria)
 END
END

If you analyzed the above procedure then there is where I declare parameter @SearchCriteria that is used for a dynamic where condition, Here I also used another string variable @StringQuery with VARCHAR(800) Datatype that contains SQL query of the select statement.

And finally, we need to execute a string as we declared @StringQuery and @SearchCriteria and we will execute this using sp_execution after successful execution, you can see the result shown in the output below.

Execute Stored Procedure

Exec Get_Employee_Detail ' Employee_Detail.EmpId IN(1,3,5) AND Employee_Detail.Country <> ''UK'''

SQL Server Dynamic Stored Procedure

Output

Output

Summary

This article explains how you can create and pass dynamic criteria at where clause using stored procedure in SQL server.