Sql Server Create and Execute Dynamic Stored Procedure

watch_later 21 November, 2018

Introduction

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 dynamic where clause in SQL query. In my previous article, I explained how to create and execute parameterized stored procedures From another stored procedure and hear I will also show you how to execute a dynamic stored procedure in 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 where clause, but in some cases, we didn't have any fixed criteria or have too many columns in where clause so at that time we require 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 requirement I have created a report for 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. So 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 Start our actual implementation we need a database, tables, and some dummy records for demonstration, So first we will create a database, 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 we are ready to start to write 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 analyzed the above procedure then there 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 and 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.

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

sentiment_satisfied Emoticon