Đang chuẩn bị liên kết để tải về tài liệu:
Microsoft SQL Server 2008 R2 Unleashed- P183

Microsoft SQL Server 2008 R2 Unleashed- P183:SQL Server 2005 provided a number of significant new features and enhancements over what was available in SQL Server 2000. This is not too surprising considering there was a five-year gap between these major releases.Microsoft SQL Server 2008 is not as much of a quantum leap forward from SQL Server 2005 | 1774 CHAPTER 44 Advanced Stored Procedure Programming and Optimization Using Dynamic SQL in Stored Procedures SQL Server allows the use of the EXEC statement in stored procedures to execute dynamic SQL statements. This capability allows you to do things such as pass in object names as parameters and dynamically execute a query against the table name passed in as in the following example IF EXISTS SELECT FROM sys.procedures WHERE schema_id schema_id dbo AND name N get_order_data DROP PROCEDURE dbo.get_order_data GO create proc get_order_data @table varchar 30 @column varchar 30 @value int as declare @query varchar 255 select @query select from @table where @column convert varchar 10 @value EXEC @query return This feature can be useful when you have to pass a variable list of values into a stored procedure. The string contains a comma-separated list of numeric values or character strings just as they would appear inside the parentheses of an IN clause. If you are passing character strings you need to be sure to put single quotation marks around the values as shown in Listing 44.14. LISTING 44.14 Passing a Variable List of Values into a Stored Procedure IF EXISTS SELECT FROM sys.procedures WHERE schema_id schema_id dbo AND name N find_books_by_type DROP PROCEDURE dbo.find_books_by_type GO create proc find_books_by_type @typelist varchar 8000 as exec select title_id title substring title 1 40 type price from titles where type in @typelist order by type title_id go Using Dynamic SQL in Stored Procedures 1775 set quoted_identifier off exec find_books_by_type business mod_cook trad_cook go title_id title type price BU1032 The Busy Executive s Database Guide business 14.9532 BU1111 Cooking with Computers Surreptitious Ba business 14.595 BU2075 You Can Combat Computer Stress business 15.894 BU7832 Straight Talk About Computers business 14.9532 MC2222 Silicon Valley Gastronomic Treats mod_cook 14.9532 MC3021 The Gourmet Microwave mod_cook 15.894 TC3218 Onions Leeks and .