Registered User Registered User - 7 months ago 19
SQL Question

Stored procedure EXEC vs sp_executesql difference?

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'

Answer

Your sp_executesql SQL should probably be;

DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + 
            @TableName + ' where Status=@eStatus'

This will allow you to call sp_executesql with @eStatus as a parameter instead of embedding it into the SQL. That will give the advantage that @eStatus can contain any characters and it will be properly escaped automatically by the database if required to be secure.

Contrast that to the SQL required for EXEC;

DECLARE @SQL as nvarchar(128) = 'select ' + @Columns + ' from ' + 
            @TableName + ' where Status=' + char(39) + @Status + char(39)

...where a char(39) embedded in @Status will make your SQL invalid and possibly create an SQL injection possibility. For example, if @Status is set to O'Reilly, your resulting SQL would be;

select acol,bcol,ccol FROM myTable WHERE Status='O'Reilly'