I've written two stored procedure one with sp_executesql
and other doesn't have sp_executesql
both are executing properly same results, I didn't get what is the difference here between
EXEC (@SQL) vs EXEC sp_executesql @SQL, N'@eStatus varchar(12)', @eStatus = @Status
and How EXEC(@SQL) is prone to SQL injection and sp_executesql @SQL...... isn't?
Below Stored Procedure without sp_executesql
ALTER proc USP_GetEmpByStatus
(
@Status varchar(12)
)
AS
BEGIN
DECLARE @TableName AS sysname = 'Employee'
Declare @Columns as sysname = '*'
DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + @TableName + ' where Status=' + char(39) + @Status + char(39)
print (@SQL)
EXEC (@SQL)
END
EXEC USP_GetEmpByStatus 'Active'
Below stored procedure with sp_executesql
create proc USP_GetEmpByStatusWithSpExcute
(
@Status varchar(12)
)
AS
BEGIN
DECLARE @TableName AS sysname = 'JProCo.dbo.Employee'
Declare @Columns as sysname = '*'
DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + @TableName + ' where Status=' + char(39) + @Status + char(39)
print @SQL
exec sp_executesql @SQL, N'@eStatus varchar(12)', @eStatus = @Status
END
EXEC USP_GetEmpByStatusWithSpExcute 'Active'
Besides the usage, there are some important differences:
sp_executesql
allows for statements to be parameterized
Therefore It’s more secure than EXEC
in terms of SQL injection
sp_executesql
can leverage cached query plans.
The TSQL string is built only one time, after that every time same query is called with sp_executesql
, SQL Server retrieves the query plan from cache and reuses it
Temp tables created in EXEC
can not use temp table caching mechanism