title: Stored procedure EXEC vs sp_executesql difference?
body: <p>I've written two stored procedure one with <code>sp_executesql</code> and other doesn't have sp_executesql
  both are executing properly same results, I didn't get what is the difference here between</p>
  
  <blockquote>
    <p>EXEC (@SQL) vs EXEC sp_executesql @SQL, N'@eStatus varchar(12)',
    @eStatus = @Status</p>
  </blockquote>
  
  <p>and How EXEC(@SQL) is prone to SQL injection and sp_executesql @SQL...... isn't?</p>
  
  <p>Below Stored Procedure without sp_executesql</p>
  
  <pre><code>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'
  </code></pre>
  
  <p>Below stored procedure with sp_executesql    </p>
  
  <pre><code>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'
  </code></pre>