EXEC vs SP_EXECUTESQL Performance

Recently we used a sql code reviewing tool against our database. It is suggesting to use SP_EXECUTESQL instead of EXEC.

I know SP_EXECUTESQL helps us to avoid sql injection. Is there any difference in performance when using EXEC vs SP_EXECUTESQL.

This is mostly a preference due to security and consistency, and has nothing to do with performance (though that may have been more of a concern in ancient versions of SQL Server).

Why use EXEC() some of the time when you should be using sp_executesql whenever you have parameters? EXEC() forces you to concatenate all of your variables into a single string, and this makes it ripe for abuse.

I wrote about this in more detail here:

• Bad Habits to Kick : Using EXEC() instead of sp_executesql

I also wrote about protecting yourself from SQL injection here:

• Protecting Yourself from SQL Injection in SQL Server - Part 1

• Protecting Yourself from SQL Injection in SQL Server - Part 2

SQL injection is a pretty big deal, and plenty of other people have written about it too.

Finally, be sure when you call system procedures that you use the proper casing to match what's stored in sys.all_objects - it should be all lower case. Otherwise, if your code gets deployed to a case sensitive instance, it will all start failing.

StackExchange Database Administrators Q#165149, answer score: 15