Stored procedure EXEC vs sp_executesql difference?

by

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'

Leave a Comment

tech