$$SWYNK$$
SQL Tip – SP_Execute_SQL
For those rare occasions when you really must build dynamic SQL for your SQL Server instance while using scripts or stored procedures. When doing this you have two options.
EXEC(‘SQL Statement’)
Or
Sp_Execute_SQL
There is a big difference between the two. The exec method operates completely as an ad-hoc query. If every command is EXACTLY the same, then it will re-use the query plan.
In contrast, the sp_execute_SQL command is a stored procedure in the Master database. This method differs in that it allows you to specify parameters for the query. Those parameters are assigned data types…and can be replaced with different values.
This method has the advantage of having a plan that allows for the variables to be modified without requiring a complete new query plan. This is highly preferred if you must perform ad-hoc commands in an SQL Server instance.
If you run an SQL Trace on your database executing parameterized queries you will find that it uses this same method of executing your query. It will establish an sp_execute_SQL command, provide the sql statement as an NVARCHAR value, establish the variables and their data types, and finally assign values to the variables.
Take a look at books online for more assistance using sp_execute_SQL.
If you’d like to share a tip with our readers, feel free to send it to me at btaylor@sswug.org
DBTechCon – Come and Learn… ONLINE
Don’t forget to check out DBTechCon coming later this month. This is much more than database stuff. SO much information, 70 sessions, 20 speakers… straight to your desktop – on-demand, downloads, how-to and live speaker interaction – get those questions answered!
[Find out more here]
Cheers,
Ben
Featured White Paper(s)
Upgrading to Microsoft SQL Server 2008 R2 from Microsoft SQL Server 2008, SQL Server 2005, and SQL Server 2000 More than ever, organizations rely on data storage and analysis for business operations. Companies need the ability to deploy… (read more)