Today at work I needed to make a simple stored procedure containing a simple search query with some dynamic ‘WHERE’ conditions. Since I am working with Miscrosoft SQL Server 2005 I could pick between the EXECUTE (or EXEC) command or the ‘sp_executesql’ system stored procedure. I knew that both options existed and until today I always figured they were just an alias for each other (shame on me). I finally decided to spend some time researching if there are any differences between the two commands. This is where I stumbled upon the following blog post from Pinal Dave: SQL SERVER – 2005 Comparison SP_EXECUTESQL vs EXECUTE/EXEC.
As explained by Pinal Dave you should use sp_executesql over the EXECUTE command because it accepts parameterized statements. Parameterized statements has a few big advantages (for more details please read Pinal Dave’s article):
- Using parameterized statements is more secure, since you don’t have to worry about SQL injection (when implemented properly of course).
- Parameterized statements give the advantage of a cached query plan, which improves performance.
- Parameterized statements also improve the readability of your SQL code.
So far so good everything is clear lets forget about the EXECUTE command and start using the ‘sp_executesql’ stored procedure from now on… Continue reading
