Create and Execute Dynamic Stored Procedure

watch_later 5/15/2023

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
SQL Server Dynamic Stored Procedure


Output

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.

Codingvila provides articles and blogs on web and software development for beginners as well as free Academic projects for final year students in Asp.Net, MVC, C#, Vb.Net, SQL Server, Angular Js, Android, PHP, Java, Python, Desktop Software Application and etc.

Thank you for your valuable time, to read this article, If you like this article, please share this article and post your valuable comments.

Once, you post your comment, we will review your posted comment and publish it. It may take a time around 24 business working hours.

Sometimes I not able to give detailed level explanation for your questions or comments, if you want detailed explanation, your can mansion your contact email id along with your question or you can do select given checkbox "Notify me" the time of write comment. So we can drop mail to you.

If you have any questions regarding this article/blog you can contact us on info.codingvila@gmail.com

sentiment_satisfied Emoticon