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

  • declare variable for query string
  • T-SQL Puzzler - Crawling Object Dependencies
  • The DELETE statement conflicted with the SAME TABLE REFERENCE constraint with Entity Framework
  • validate a date in t-sql?
  • .NET: How to insert XML document into SQL Server
  • How to get result from parent child table
  • 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'
    

    4 Solutions collect form web for “Stored procedure EXEC vs sp_executesql difference?”

    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'
    

    Besides the usage, there are some important differences:

    1. sp_executesql allows for statements to be parameterized
      Therefore It’s more secure than EXEC in terms of SQL injection

    2. 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

    3. Temp tables created in EXEC can not use temp table caching mechanism

    With sp_executesql, you don’t have to build your query like that. You could declare it like this:

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

    This way if your @Status value came from a user you can use @eStatus and not have to worry about escaping '. sp_executesql gives you the ability to put variables in your query in string form, instead of using concatenation. So you have less to worry about.

    The column and table variables are still the same, but that’s less likely to be directly from a user.

    With Exec You can’t have a place holder in your T-Sql statement string.

    sp_executesql
    gives you the advantage of having a place holder and pass the actual value at runtime

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.