SQL sp_executesql versus EXECUTE

17 April,2013 by Jack Vamvas

Question: Could you explain the difference between sql sp_executesql and EXECUTE (EXEC) ?

Answer:  There are a number of similarities and differences of SQL sp_executesql and EXEC. Let’s start by looking at the SQL Server BOL definitions:

Sp_executesql = “Executes a Transact-SQL statement or batch that can be reused many times, or one that has been built dynamically. The Transact-SQL statement or batch can contain embedded parameters”

EXECUTE = “Executes a command string or character string within a Transact-SQL batch, or one of the following modules: system stored procedure, user-defined stored procedure, scalar-valued user-defined function, or extended stored procedure.

 Notes on sp_executesql

1) The ad-hoc T-SQL statements are paramaterized.

2) The SQL Server Optimizer reuses the execution plan generated as the T-SQL appears static to the SQL Server Query Optimizer , while only the parameters are changing. This is an advantage ,helping to decrease recompilation cost and improve execution plan reuse. The disadvantage is if a “bad” plan is created , it continues to be reused at every execution

3) SQL Injection Protection - Strong typed parameters assist in defending against SQL Injection attacks.

 Notes on EXECUTE

1) Executes any statement construct. Mainly used for stored procedures , but also other character strings

2) EXECUTE does not have strong typed parameters

3) Execution Plans generated by EXECUTE aren’t always cached.

Switching from sql sp_executesql to EXECUTE is not a guarantee of improved sql performance. I’ve seen plenty of situations where the first execution created a “bad” plan. Testing and monitoring is important to optimize response times . I find wrapping up sp_executesql  in a stored procedure is an easier method to manage the  variable options, as well as offering futher benefits to be maintained as part of  a consistent code base

Read More

SQL Server - Stored Procedures versus ad-hoc paramaterized queries.Which is faster?

SQL Server - Stored Procedure Checklist - SQL Server DBA

SQL Server - How to improve Execution Plan Reuse - SQL Server DBA


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on SQL sp_executesql versus EXECUTE


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer