Hello Friends, In this blog, we will see how we can execute Dynamic Query in SQL Server. Sometimes you need to generate dynamic SQL queries that built on fly and execute. Here we are going to see how we can execute dynamic queries in SQL Server.

Introduction to Dynamic Query:

There are two approaches to execute dynamic query in SQL server

  1.  Using Execute.
  2. Using sp_executeSQL.

Before starting let’s create a sample Employee table and insert few demo records into it.

Create Table:

CREATE TABLE [dbo].[Employee](
[EmployeeID] [bigint] IDENTITY(1,1) NOT NULL,
[EmployeeName] [varchar](100) NULL,
[Designation] [varchar](50) NULL,
[Salary] [float] NULL,
[City] [varchar](100) NULL
) ON [PRIMARY]

GO

Insert Records:

SET IDENTITY_INSERT [dbo].[Employee] ON

GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Designation], [Salary], [City]) VALUES (1, N'Ahwin Roy', N'Software Engineer', 800000, N'Varanasi')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Designation], [Salary], [City]) VALUES (2, N'Ritesh Shah', N'Team Leader', 900000, N'New Delhi')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Designation], [Salary], [City]) VALUES (3, N'Vikas Gupta', N'DBA', 1200000, N'Allahabad')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Designation], [Salary], [City]) VALUES (4, N'Akash Tripathi', N'Tester', 700000, N'Mumbai')
GO
INSERT [dbo].[Employee] ([EmployeeID], [EmployeeName], [Designation], [Salary], [City]) VALUES (5, N'Nitin Pandey', N'Web Developer', 8000000, N'Pune')
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF

1- Using Exec:

Using Exec you can build your select statement on fly and execute that with EXEC. You can add your columns and where condition as per your need.

Example:

DECLARE @SQLQuery varchar(max)
DECLARE @columnList varchar(75)
DECLARE @pCity varchar(100)
SET @columnList = '[EmployeeID], [EmployeeName], [Designation],[Salary]'
SET @pCity = '''Varanasi'''
SET @SQLQuery = 'SELECT ' + @columnList + ' FROM [dbo].[Employee] WHERE City = ' + @pCity
EXEC (@SQLQuery)

Output:

Dynamic Query

2- Sp_executesql:

Using this approach you can build dynamic query similar to EXEC but you can also pass the parameter.

Example:

DECLARE @SQLQuery nvarchar(MAX)
DECLARE @columnList varchar(75)
DECLARE @pCity varchar(100)
SET @columnList = '[EmployeeID], [EmployeeName], [Designation],[Salary]'
SET @pCity = 'Varanasi'
SET @SQLQuery = 'SELECT ' + @columnList + ' FROM [dbo].[Employee] WHERE City = @pCity'
EXECUTE sp_executesql @SQLQuery, N'@pCity nvarchar(100)', @pCity = @pCity

Output:

Dynamic Query

View More:

Conclusion:

I hope this tutorial about Dynamic Query in SQL Server. I would like to have your feedback, comments, and suggestions.

Thank You.

Leave a Comment