Home » SQL Server sp_executesql

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.

@param_name1 datatype,.. defines the parameter’s name and data type which are used in the dynamically constructed SQL statements.

If the SQL  statement or batch in @sqlquery does not contain parameters, in this case @param_name1 datatype,.. is not required. The default value for this parameter is NULL.

@param_name1=‘Value1’,..  is used to define the value for parameter that is defined in the parameter string.

[Out | Output] defines that the parameter is an output parameter.

Lets look at an example of sp_executesql in SQL Server.

First we Create a sample table named Employee in SQL Server, then Insert some records into table.
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 assgined to variable @SqlQuery, and that is executed by sp_executesql stored procedure.

 

Using sp_executesql with simple Dynamic SQL statement

Following dynamically constructed SQL Statement returns the employee details from Employee table for employee id 508.

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 construced 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 empoyees is three whose birth day year is 1980.

Recommeded for you

SQL Server Sp_help

SQL Server Sp_rename

SQL Server sp_monitor




Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.