Write And Execute Dynamic SQL Query In SQL Server

Introduction

This example demonstrates how to write and execute a dynamic SQL Query in SQL Server. You can execute a Query using EXEC sql command and sp_executesql commands in SQL Server.

The Execution of dynamic SQL Query is supported in SQL Server versions like SQL Server 2000, SQL Server 2005, SQL Server 2008, SQL Server 2008R2, SQL Server 2012, SQL Server 2014 or higher versions of SQL Server.

First Create Database for Proper Demontration.

What is a Database ?

Database is a structured set of data held in a computer that can be accessed in various ways. In short, the database stores informative data in tabular format. One Database can contain multiple tables.

What are Tables ?

A table is made up of rows and columns.A table has a specified number of columns, but can have any number of rows. In the Table each row in a relational is uniquely identified by a primary key.

How to Create Table in Database ?

You can Create table by writing sql statement "CREATE TABLE".

Example

CREATE TABLE #Temp
(
EmployeeId INT,
EmployeeName VARCHAR(50),
Department VARCHAR(50),
)

//#Temp is Table Name

What is primary key ?

Primary key is a unique identifier. It is a key in a relational database that is unique for each record.

How to Insert Record in Created Table ?

You can Insert Record in Created Table #Temp by Write Sql Statement "INSERT INTO"

Example

INSERT INTO #Temp (EmployeeId, EmployeeName, Department) VALUES (1,'Nikunj Satasiya','Asp.Net')
INSERT INTO #Temp (EmployeeId, EmployeeName, Department) VALUES (1,'Hiren Dobariya','Asp.Net')
INSERT INTO #Temp (EmployeeId, EmployeeName, Department) VALUES (1,'Vivek Ghadiya','Android')
INSERT INTO #Temp (EmployeeId, EmployeeName, Department) VALUES (1,'Pratik Pansuriya','SEO')
INSERT INTO #Temp (EmployeeId, EmployeeName, Department) VALUES (1,'Sneha Patel','PHP')
INSERT INTO #Temp (EmployeeId, EmployeeName, Department) VALUES (1,'Sarah Demola','iOS')

You can Get Inserted Data By Writing Sql Statement "SELECT"

SELECT * FROM #Temp WITH (NOLOCK)

So, let's write and execute dynamic SQL Query. Generally you can do as follows: You simply concatenate the parameter values to the SQL string.

Example

CREATE PROCEDURE Employee_GetEmployee
@EmployeeId CHAR(5)
AS
BEGIN
DECLARE @SQL NVARCHAR(2000)
SET @SQL = 'SELECT EmployeeName FROM #Temp WHERE EmployeeId = @EmployeeId'
EXEC sp_executesql @SQL, N'@EmployeeId CHAR(5)', @EmployeeId = @EmployeeId
END

Summary

It's difficult to handle due to single quotes and also it is vulnerable to attacks like SQL Injection and hence you must make use of sp_executesql function and pass the parameter value. in the above stored procedure, parameter values are passed to the dynamic SQL in SQL Server.