difference between exec and sp_executeSql

both are used for executing dynamic sql in sql server.
both are not preferred because, If we are using dynamic sql in stored procedure, SQL Server may not use the execution plan. It will recreate the execution plan every time with different string of SQL.So, we have to think about the performance while using dynamic sql.

Main Difference:

Exec is non parametrized
sp_executeSql is parametrized

the main difference between above two is difference in performance.
the for Exec the execution plan is created every time it gets executed where as with sp_executeSql the same execution plan (will try) will be used only its value will be changed.

but if both's queries are getting changed on every call than there is no difference between them. but even though the plus point is with sp_executeSql that if their comes same query to it than it use same execution plan with different parameter and perform operation faster with compare to Exec.

Example:

DECLARE @ItemID INT
DECLARE @Query NVARCHAR(200)

SET @Query = 'SELECT * FROM [dbo].[Item] WHERE ID = '

SET @ItemID = 1
EXEC( @Query + @ItemID)

SET @ItemID = 2
EXEC( @Query + @ItemID)

SET @Query = 'SELECT * FROM [dbo].[Item] WHERE ID = @ID'

SET @ItemID = 1
EXEC sp_executesql @Query, N'@ID INT', @ID = @ItemID -- this will be faster because its only value will be changed and will use same execution plan.

Solution By:

Rajesh Rolen