Create and Execute Dynamic Stored Procedure

In this article, I am going to explain how to create a dynamic stored procedure in SQL Server and also explain how you can pass a dynamic WOW clause in a SQL query. In my previous article, I explained how to create and run stored procedures with parameters from another stored procedure, and here I will also show you how to execute a dynamic stored procedure in SQL Server for example.

While we work with any data-driven application, sometimes there is a need 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 table using where clause but in some cases we didn’t have fixed criteria or we have too many columns in where clause so at that time we required dynamic where statement where we can pass where clause dynamically As per our requirement in SQL server.

demand

Today, the same thing is actually happening to me according to my client’s request. I created a report for the stock market for a month and a year and I have several tables and displays with too much data on the tables and from that, I needed several columns but I did not do and fixed criteria to go through where section and filter records from a table and get filtered records From tables only, the user may be search records with year, month, inventory date, entry date, RGC date, RGC class, type clause etc. To store this kind of requirement I have written a dynamic stored procedure where I will pass dynamic criteria in the where clause to filter data from a table.

Application

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

Create a database

CREATE DATABASE db_Employee

Create a 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
)

Add records to the 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 a few records and ready to start writing a dynamic stored procedure in 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 analyze the procedure above then there is the place where I declare the @SearchCriteria parameter which is used for the dynamic where condition, here I have also used another string variable @StringQuery with VARCHAR(800) Datatype which contains the SQL query of the select statement.

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

Execute the stored procedure

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

Productivity

Productivity

Summary

This article explains how to create and pass dynamic criteria in a where clause using a SQL Server stored procedure.