Tham khảo tài liệu 'expert sql server 2008 development- p6', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | CHAPTER 8 DYNAMIC T-SQL The static SQL version as expected still wins from a performance point of view although all three are extremely fast . Again more complex stored procedures with longer runtimes will naturally overshadow the difference between the dynamic SQL and static SQL solutions leaving the dynamic SQL vs. static SQL question purely one of maintenance. Note When running these tests on my system I restarted my SQL Server service between each run in order to ensure absolute consistency. Although this may be overkill for this case you may find it interesting to experiment on your end with how restarting the service affects performance. This kind of test can also be useful for general scalability testing especially in clustered environments. Restarting the service before testing is a technique that you can use to simulate how the application will behave if a failover occurs without requiring a clustered testing environment. Output Parameters Although it is somewhat of an aside to this discussion I would like to point out one other feature that sp_executesql brings to the table as compared to execute one that is often overlooked by users who are just getting started using it. sp_executesql allows you to pass parameters to dynamic SQL just like to a stored procedure and this includes output parameters. Output parameters become quite useful when you need to use the output of a dynamic SQL statement that perhaps only returns a single scalar value. An output parameter is a much cleaner solution than having to insert the value into a table and then read it back into a variable. To define an output parameter simply append the output keyword in both the parameter definition list and the parameter list itself. The following T-SQL shows how to use an output parameter with sp_executesql DECLARE @SomeVariable int EXEC sp_executesql N SET @SomeVariable 123 N @SomeVariable int OUTPUT @SomeVariable OUTPUT As a result of this T-SQL the @SomeVariable variable will have a .
