SQL Server sp_executesql

The sp_executesql is a SQL Server built-in sytem stored procedure that enables to execute of the dynamically constructed SQL statements or batches.

The following are advantages of using sp_executesql to execute dynamic SQL:

• It allows you to pass parameters to dynamic SQL Statement.
• It is secure to SQL injection attacks due to parameters being strongly typed.
• Executes a SQL statement, or batch that can be reused multiple times.

SYNTAX

sp_executesql @sqlquery,
N'@param_name1 data_type [Out | Output],...n 
,@param1 = ] 'value1',...n

@sqlquery parameter is used to specify dynamically generated SQL statement or batch. Data type for @sqlquery must be either a Unicode constant or a Unicode variable because we have to add N prefix for the direct text usage or have to use nvarchar or nchar data typed variables.

CREATE TABLE dbo.Employee (EmpId INT NOT NULL, 
EmpName VARCHAR(50), 
EmpCode VARCHAR(15), 
Dob DATE)

INSERT INTO dbo.Employee(EmpId, EmpName, EmpCode, Dob)
VALUES(
501, 'RAJESH MITAL', 'XCV567', '1976-11-11'),
(502, 'SUJOY SINGH', 'XCB787', '1970-10-19'),
(503, 'ABHISHEK MISHRA', 'FJV542', '1976-09-19'),
(504, 'HARSHIT KAPOOR', 'NMD557', '1980-12-10'),
(505, 'MANISH RAWAT', 'QJB987', '1985-02-15'),
(506, 'DEEPAK KUMAR', 'FZV704', '1986-11-05'),
(507, 'HARSH KUMAWAT', 'NET657', '1986-07-01'),
(508, 'ROZER JR', 'DDB987', '1987-08-02'),
(509, 'BALAJI NEGI', 'KL0542', '1980-09-03'),
(510, 'NARAYANA SWAMI', 'NSD447', '1980-10-10'),
(511, 'LOKESH MANTRI', 'BBB007', '1990-05-19'),
(512, 'SUMIT VIYAS', 'FFNM04', '1990-06-29'),
(513, 'BABA NAYAK', 'NEG157', '1989-07-13')

Now we have a table Employee in SQL Server.

SELECT * FROM dbo.Employee

Lets implement the dynamic SQL statement and execute it using sp_executesql.

Sp_executesql without parameter

You can execute dynamically constructured SQL statement that requres no parameters using sp_executesql, this is the simplest use of sp_executesql.

The following dynamically constructured SQL returns all the employees from Employee table.

DECLARE  @SqlQuery AS NVARCHAR(4000)

SET @SqlQuery = 'SELECT * FROM dbo.Employee'
   
EXECUTE sp_executesql @SqlQuery

Lets understand T-SQL code, dynamic SQL statement that fetches the data from Employee tables is assigned to variable @SqlQuery, and that is executed by sp_executesql stored procedure.

Using sp_executesql with simple Dynamic SQL statement

DECLARE  @SqlQuery AS NVARCHAR(4000)

SET @SqlQuery = 'SELECT * FROM dbo.Employee WHERE EmpId = @EmpId'
   
EXECUTE sp_executesql @SqlQuery, N'@EmpId INT', @EmpId =   508 ;

Lets understand the above SQL statement, the dynamically constructed SQL statement is assigned to the @SqlQuery variable, then Employee table data is filtered with the parameter @EmpId of data type of  INT.  Value 508 is passed to this parameter which filters the Employee table records for Empid 508.

Once you execute the above dynamic SQL Statement, you will see it returns details of Employee for EmpId 508.

Using sp_executesql with Output Parameter in Dynamic SQL Statement

You can also use Output Parameter with sp_executesql.

Following SQL Statement returns the number of employee’s count in Employee table whose birth day year is 1980 and returns it in the output parameter @Output.

DECLARE @SqlQuery AS NVARCHAR(4000) 
DECLARE @Output AS INT 

SET @SqlQuery='SELECT @Count= COUNT(EmpId) FROM dbo.Employee 
WHERE YEAR(Dob)=@DobYear'

EXEC sp_executesql @SqlQuery, N'@DobYear INT, @Count INT OUTPUT',
@DobYear = 1980, @Count = @Output OUTPUT

SELECT @Output AS EmployeeCount

Lets understand T-SQL code, the dynamically constructed SQL statement is assigned to variable @sqlQuery,  which counts the Total employees in the Employee table whose data of birth year is ‘1980’ and set the return value to a variable with the OUTPUT parameter that is @Count , then this parameter value is assigned to the @Output  parameter.

And you can see, it returns total number of employees is three whose birth day year is 1980.

Also Read..

SQL Server Sp_help

SQL Server Sp_rename

SQL Server sp_monitor