tailieunhanh - 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 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 WHERE schema_id schema_id dbo AND name N get_order_data DROP PROCEDURE 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 . LISTING Passing a Variable List of Values into a Stored Procedure IF EXISTS SELECT FROM WHERE schema_id schema_id dbo AND name N find_books_by_type DROP PROCEDURE 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 BU1111 Cooking with Computers Surreptitious Ba business BU2075 You Can Combat Computer Stress business BU7832 Straight Talk About Computers business MC2222 Silicon Valley Gastronomic Treats mod_cook MC3021 The Gourmet Microwave mod_cook TC3218 Onions Leeks and .