These functions are commonly used for the same thing which is execute a piece of SQL code or stored procedure, most commonly used for dynamic SQL.
So lets see a usage example for both:
EXEC ( )
DECLARE @SQL varchar(max) DECLARE @Tablename varchar(20) SET @Tablename = 'Person.Address' SET @SQL = 'SELECT * FROM '+ @Tablename EXEC (@SQL)
DECLARE @SQL nvarchar(max) DECLARE @Tablename nvarchar(20) SET @Tablename = 'Person.Address' SET @SQL = 'SELECT * FROM '+ @Tablename exec sp_executesql @SQL
In the above examples there is just one slight difference, @SQL variable is varchar for EXEC and nvarchar for sp_executesql.
But what are the main differences between them?
Simple and Raw query execution
Accepts varchar as a parameter, so it accepts longer queries than sp_executesql()
Varchar Max = 8000
Provides more security and performance.
Allows query to be parameterized, see example:
DECLARE @ExecStr NVARCHAR(4000); SELECT @ExecStr = 'SELECT * FROM Person.Address WHERE Postalcode LIKE @zip'; EXEC sp_executesql @ExecStr, N'@zip varchar(15)', '98011';
Prevents SQL Injection
Creates an execution plan
Accepts Nvarchar(max) = 4000 (less than exec())
It is always recommended to use sp_executesql () rather than EXEC for the above benefits, specially on production environments.