Sql Server Create and Execute Dynamic Stored Procedure

watch_later Wednesday, November 21, 2018

Introduction

In this article i am going to explain how to create dynamic stored procedure in 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 procedure From another stored procedure and hear i will also show you how to execute dynamic stored procedure in sql server with example.

While we working with any data driven application sometime it's becomes necessary to get better and faster output and fetch data from database much as faster. 

Sometimes as per our requirement we need to get required data/information from table using where clause, but in some cases we did'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, same thing happens with me actually as per my client requirement i creation report for month and year wise stock exchange and i have multiple tables and views with too many data on tables and from that i required some columns but i did't and any fixed criteria to pass where clause and filter records from table and get filtered records from tables only, 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 to requirement i have written dynamic stored procedure where i will pass dynamic criteria in where clause to filter data from 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 by passing string variable as criteria at where clause of select statement.
4) Execute Dynamic Stored Procedure in Sql Server.

Implementation


Before Start our actual implementation we need database, tables and some dummy records for demonstration, So first we will create database, tables and also insert some dummy record in 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 table with few records and we are ready for start to write 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 above procedure then there where  i declare parameter @SearchCriteria that is used for dynamic where condition, Here i also used another string variable @StringQuery with VARCHAR(800) Datatype that contains sql query of select statment.

And finally we need to execute string as we declared @StringQuery and @SearchCriteria and we will execute this using sp_execution and after successful execution you can see result shown in 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.



sentiment_satisfied Emoticon