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.
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
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 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..
6,015 total views, 1 views today