[转]Dynamic SQL & Stored Procedure Usage in T-SQL

转自：http://www.sqlusa.com/bestpractices/training/scripts/dynamicsql/

Dynamic SQL & Stored Procedure Usage in T-SQL
Important security article related to dynamic SQL: How To: Protect From SQL Injection in ASP.NET ------------ -- Dynamic SQL QUICK SYNTAX ------------ USE AdventureWorks2008; EXEC ('SELECT * FROM Sales.SalesOrderHeader') DECLARE @DynamicSQL varchar(256); SET @DynamicSQL='SELECT * FROM Sales.SalesOrderHeader' EXEC (@DynamicSQL) GO DECLARE @DynamicSQL varchar(256), @Table sysname; SET @DynamicSQL='SELECT * FROM'; SET @Table = 'Sales.SalesOrderHeader' SET @DynamicSQL = @DynamicSQL+' '+@Table PRINT @DynamicSQL  -- for testing & debugging EXEC (@DynamicSQL) GO -- Dynamic SQL for rowcount in all tables DECLARE @DynamicSQL nvarchar(max), @Schema sysname, @Table sysname; SET @DynamicSQL = '' SELECT @DynamicSQL = @DynamicSQL + 'SELECT '''+QUOTENAME(TABLE_SCHEMA)+'.'+ QUOTENAME(TABLE_NAME)+''''+ '= COUNT(*) FROM '+ QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME) +';' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' PRINT @DynamicSQL                 -- test & debug EXEC sp_executesql @DynamicSQL    -- sql server sp_executesql -- Equivalent code using the undocumented sp_MSforeachtable EXEC sp_MSforeachtable 'select ''?'', count(*) from ?' ------------ -- Dynamic sort with collation - Dynamic ORDER BY - SQL dynamic sorting DECLARE @SQL nvarchar(max)='SELECT FullName=FirstName+'' ''+Lastname FROM AdventureWorks2008.Person.Person ORDER BY LastName ' DECLARE @Collation nvarchar(max) = 'COLLATE SQL_Latin1_General_CP1250_CS_AS' SET @SQL=@SQL + @Collation PRINT @SQL EXEC sp_executeSQL @SQL ------------ -- sp_executeSQL usage with input and output parameters DECLARE @SQL NVARCHAR(max), @ParmDefinition NVARCHAR(1024) DECLARE @Color varchar(16) = 'Blue', @LastProduct varchar(64) SET @SQL =       N'SELECT @pLastProduct = max(Name) FROM AdventureWorks2008.Production.Product WHERE Color = @pColor' SET @ParmDefinition = N'@pColor varchar(16), @pLastProduct varchar(64) OUTPUT' EXECUTE sp_executeSQL @SQL, @ParmDefinition, @pColor = @Color, @pLastProduct=@LastProduct OUTPUT SELECT Color=@Color, LastProduct=@LastProduct /* Color    LastProduct Blue  Touring-3000 Blue, 62 */ ---------- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ The following dynamic SQL scripts demonstrate: 1. Dynamic SQL stored procedure 2. Dynamic SQL with OUTPUT parameter 3. Stored procedure with dynamic SQL WHILE loop 4. Dynamic SQL with using parent's #temptable 5. Dynamic SQL for dynamic PIVOT query 6. Dynamic stored procedure with output parameter 7. WHERE clause with dynamic set of predicates ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ IMPORTANT SECURITY ARTICLE: Is Dynamic SQL in Your Stored Procedures Vulnerable to SQL Injection? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -- FIRST EXAMPLE - dynamic stored procedure for customer list USE Northwind; GO -- DROP stored procedure if exists to make CREATE work IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CustomerListByState]') AND TYPE IN (N'P',N'PC')) DROP PROCEDURE [dbo].[CustomerListByState] GO -- Sproc (stored procedure) with dynamic SQL /***** DEMO ONLY - This sproc is vulnerable to SQL Injection Attack *****/ -- List splitter and JOIN is the preferred solution CREATE PROCEDURE CustomerListByState @States VARCHAR(128) AS BEGIN SET NOCOUNT ON DECLARE @SQL NVARCHAR(MAX) -- alternate nvarchar(1024) -- Dynamic query assembly with string concatenation SET @SQL = 'select Region, CustomerID, CompanyName, ContactName, Phone from Customers where Region IN (' + @States + ')' + ' order by Region, CompanyName' PRINT @SQL -- for testing & debugging /* Assembled code select Region, CustomerID, CompanyName, ContactName, Phone from Customers where Region IN ('WA', 'OR', 'ID', 'CA') order by Region, CompanyName */ EXEC sp_executeSQL @SQL END GO -- Execute dynamic SQL stored procedure script DECLARE @States VARCHAR(100) SET @States = '''WA'', ''OR'', ''ID'', ''CA''' EXEC CustomerListByState @States GO /* Results Region CustomerID CompanyName ContactName Phone CA LETSS Let's Stop N Shop Jaime Yorres [phone] ID SAVEA Save-a-lot Markets Jose Pavarotti [phone] OR GREAL Great Lakes Food Market Howard Snyder [phone] OR HUNGC Hungry Coyote Import Store Yoshi Latimer [phone] OR LONEP Lonesome Pine Restaurant Fran Wilson [phone] OR THEBI The Big Cheese Liz Nixon [phone] WA LAZYK Lazy K Kountry Store John Steel [phone] WA TRAIH Trail's Head Gourmet Provisioners Helvetius Nagy [phone] WA WHITC White Clover Markets Karl Jablonski [phone] */ -- SECOND EXAMPLE - search names in Person.Person table -- Dynamic SQL with input and output parameters USE AdventureWorks2008; DECLARE  @ParmDefinition NVARCHAR(1024) = N'@FirstLetterOfLastName char(1), @LastFirstNameOUT nvarchar(50) OUTPUT' DECLARE @FirstLetter CHAR(1) = 'P', @LastFirstName NVARCHAR(50) DECLARE @SQL NVARCHAR(MAX) = N'SELECT @LastFirstNameOUT = max(FirstName) FROM Person.Person'+CHAR(13)+ 'WHERE left(LastName,1) = @FirstLetterOfLastName' PRINT @SQL+CHAR(13)     -- test & debug PRINT @ParmDefinition    -- test & debug EXECUTE sp_executeSQL @SQL, @ParmDefinition, @FirstLetterOfLastName = @FirstLetter, @LastFirstNameOUT=@LastFirstName OUTPUT SELECT [Last First Name] = @LastFirstName, Legend='of last names starting with', Letter=@FirstLetter GO /* Results Last First Name   Legend                        Letter Zoe               of last names starting with   P */ -- THIRD EXAMPLE - SPROC to enumerate all objects in databases -- Return objects count in all databases on the server -- Dynamic SQL stored procedure with cursor loop -- QUOTENAME function is used to build valid identifiers USE AdventureWorks; GO IF EXISTS (SELECT * FROM   sys.objects WHERE  object_id = OBJECT_ID(N'[dbo].[sprocObjectCountsInAllDBs]') AND TYPE IN (N'P',N'PC')) DROP PROCEDURE [dbo].[sprocObjectCountsInAllDBs] GO CREATE PROC sprocObjectCountsInAllDBs AS BEGIN DECLARE  @dbName      SYSNAME, @ObjectCount INT DECLARE  @SQL NVARCHAR(MAX) DECLARE  @DBObjectStats  TABLE( DBName    SYSNAME, DBObjects INT ) DECLARE curAllDBs CURSOR  FOR SELECT   name FROM     MASTER.dbo.sysdatabases WHERE    name NOT IN ('master','tempdb','model','msdb') ORDER BY name OPEN curAllDBs FETCH  curAllDBs INTO @dbName WHILE (@@FETCH_STATUS = 0) -- Loop through all db-s BEGIN -- Build valid yet hard-wired SQL statement SET @SQL = 'select @dbObjects = count(*)' + char(13) + 'from ' + QuoteName(@dbName) + '.dbo.sysobjects' PRINT @SQL -- Use it for debugging /* select @dbObjects = count(*) from [AdventureWorks].dbo.sysobjects */ -- Dynamic call for query execution with output parameter(s) EXEC sp_executesql @SQL, N'@dbObjects int output', @dbObjects = @ObjectCount output INSERT @DBObjectStats SELECT @dbName, @ObjectCount FETCH  curAllDBs INTO @dbName END -- while CLOSE curAllDBs DEALLOCATE curAllDBs -- Return results SELECT * FROM @DBObjectStats ORDER BY DBName END GO -- Execute stored procedure EXEC sprocObjectCountsInAllDBs GO /* Partial results DBName                  DBObjects AdventureWorks          604 AdventureWorks2008      646 AdventureWorksDW        151 AdventureWorksDW2008    164 AdventureWorksLT        158 AdventureWorksLT2008    158 */ /* FOURTH EXAMPLE - automatic T-SQL code generation         for datetime conversion from style 0 to 14 */ USE AdventureWorks2008; DECLARE  @I INT = -1 DECLARE  @SQLDynamic NVARCHAR(1024) -- Temporary table is used for data sharing between parent & child processes -- This is the parent process; the child process is the dynamic SQL execution CREATE TABLE #SQL ( STYLE  INT, [SQL]  VARCHAR(256), Result VARCHAR(32)) -- Loop on @I from 0 to 13 WHILE (@I < 14) BEGIN SET @I += 1 -- Store query and dynamic results in temporary table INSERT #SQL (STYLE, [SQL]) SELECT @I, 'SELECT ' + 'CONVERT(VARCHAR, GETDATE(), ' + CONVERT(VARCHAR,@I) + ')' -- Build dynamic sql statement SET @SQLDynamic = 'UPDATE #SQL SET Result=(SELECT  CONVERT(VARCHAR, GETDATE(), ' + CONVERT(VARCHAR,@I) + ')) WHERE STYLE=' + CONVERT(VARCHAR,@I) PRINT @SQLDynamic /* UPDATE #SQL SET Result=(SELECT  CONVERT(VARCHAR, GETDATE(), 0)) WHERE STYLE=0 */ EXEC sp_executeSQL  @SQLDynamic END -- Return results from temporary table SELECT * FROM   #SQL DROP TABLE #SQL GO /* Partial results STYLE SQL                                       Result 0     SELECT CONVERT(VARCHAR, GETDATE(), 0)     Mar 14 2009  6:10AM 1     SELECT CONVERT(VARCHAR, GETDATE(), 1)     03/14/09 2     SELECT CONVERT(VARCHAR, GETDATE(), 2)     09.03.14 */ -- FIFTH EXAMPLE - dynamic pivot crosstab query ------------ -- T-SQL Dynamic Pivot Crosstab Report - Column header YYYY is dynamically assembled ------------ USE AdventureWorks GO DECLARE  @YearList  AS  TABLE( YYYY INT    NOT NULL    PRIMARY KEY ) DECLARE  @DynamicSQL  AS NVARCHAR(MAX) INSERT INTO @YearList SELECT DISTINCT YEAR(OrderDate) FROM   Sales.SalesOrderHeader DECLARE  @ReportColumnNames  AS NVARCHAR(MAX), @IterationYear      AS INT SET @IterationYear = (SELECT MIN(YYYY) FROM   @YearList) SET @ReportColumnNames = N'' -- Assemble pivot list dynamically WHILE (@IterationYear IS NOT NULL) BEGIN SET @ReportColumnNames = @ReportColumnNames + N',' + QUOTENAME(CAST(@IterationYear AS NVARCHAR(10))) SET @IterationYear = (SELECT MIN(YYYY) FROM   @YearList WHERE  YYYY > @IterationYear) END SET @ReportColumnNames = SUBSTRING(@ReportColumnNames,2,LEN(@ReportColumnNames)) PRINT @ReportColumnNames -- [2001],[2002],[2003],[2004] SET @DynamicSQL = N'SELECT * FROM (SELECT [Store (Freight Summary)]=s.Name, YEAR(OrderDate) AS OrderYear, Freight = convert(money,convert(varchar, Freight)) FROM Sales.SalesOrderHeader soh INNER JOIN Sales.Store s ON soh.CustomerID = s.CustomerID) as Header PIVOT (SUM(Freight)    FOR OrderYear IN(' + @ReportColumnNames + N'))  AS Pvt ORDER BY 1' PRINT @DynamicSQL -- Testing & debugging /* SELECT * FROM (SELECT [Store (Freight Summary)]=s.Name, YEAR(OrderDate) AS OrderYear, Freight = convert(money,convert(varchar, Freight)) FROM Sales.SalesOrderHeader soh INNER JOIN Sales.Store s ON soh.CustomerID = s.CustomerID) as Header PIVOT (SUM(Freight)    FOR OrderYear IN([2001],[2002],[2003],[2004]))  AS Pvt ORDER BY 1 */ -- Execute dynamic sql EXEC sp_executesql   @DynamicSQL GO -- Partial results Store (Freight Summary) 2001 2002 2003 2004 A Bike Store 921.55 1637.24 NULL NULL A Great Bicycle Company 142.08 114.34 15.24 NULL A Typical Bike Shop 976.61 1529.08 NULL NULL Acceptable Sales & Service 12.58 25.17 NULL NULL Accessories Network NULL NULL 24.72 43.06 Acclaimed Bicycle Company NULL NULL 190.01 53.8 Ace Bicycle Supply NULL 21.46 21.67 69.46 ------------ -- SIXTH EXAMPLE - dynamic stored procedure with output -- SQL Server dynamic SQL stored procedure to find size for all databases CREATE PROC sprocSizeForAllDBs AS BEGIN DECLARE  @dbName      SYSNAME, @ObjectSize INT DECLARE  @SQL NVARCHAR(MAX) DECLARE  @DBSizes  TABLE( DBName             SYSNAME, DBSizeinMB       MONEY ) DECLARE curAllDBs CURSOR  FOR SELECT   name FROM     MASTER.dbo.sysdatabases WHERE    name NOT IN ('master','tempdb','model','msdb') ORDER BY name OPEN curAllDBs FETCH  curAllDBs INTO @dbName WHILE (@@FETCH_STATUS = 0) -- Loop through all db-s BEGIN -- Build valid yet hard-wired SQL statement SET @SQL = 'select @DBSize = 0.0078125 * sum(size) ' + char(13) + 'from ' + QuoteName(@dbName) + '.dbo.sysfiles' PRINT @SQL -- test & debug /* select @DBSize = 0.0078125 * sum(size) from [AdventureWorks].dbo.sysfiles */ -- Dynamic call for query execution with output parameter(s) EXEC sp_executesql  @SQL , N'@DBSize Money output' , @DBSize = @ObjectSize OUTPUT INSERT @DBSizes SELECT @dbName,  @ObjectSize FETCH  curAllDBs INTO @dbName END -- while CLOSE curAllDBs DEALLOCATE curAllDBs INSERT @DBSizes  -- total size SELECT 'Total Space Used', SUM(DBSizeinMB) FROM @DBSizes -- Return results SELECT * FROM     @DBSizes ORDER BY DBSizeinMB DESC END -- sproc GO EXEC sprocSizeForAllDBs /* DBName                    DBSizeinMB .... AdventureWorks            172.00 AdventureWorks2008        182.00 AdventureWorksDW          69.00 AdventureWorksDW2008      87.00 ..... */ ------------ -- SEVENTH EXAMPLE - dynamic WHERE clause -- Dynamic SQL logic to search a set of keywords in text USE tempdb; CREATE TABLE [Text] (Line nvarchar(max)) INSERT [Text] VALUES ('microsoft.com SQL web page quote: Line-of-business applications (LOB) are the critical link between the IT department and the business. The ability to securely and reliably store, centralize, manage and distribute data out to users is key to these LOB applications. SQL Server 2008 provides businesses with a high performance database platform that’s reliable, scalable, and easy to manage. SQL Server 2008 R2 builds on the 2008 release and helps IT departments provide even more cost-effective scalability on today’s most advanced hardware platforms using familiar SQL Server administration tools.') DECLARE @Keyword TABLE ( Search varchar(32)) INSERT @Keyword VALUES ('reliable'), ('scalability'), ('centralize') -- Dynamic SQL string variable DECLARE @SQL nvarchar(max) = 'SELECT Result=''FOUND'' FROM [Text] WHERE 1 != 1' -- Cursor WHILE loop to add all search word predicates to WHERE clause /******* THIS IS THE DYNAMIC PART *********/ DECLARE @Search varchar(32) DECLARE curKeyword CURSOR FOR SELECT Search FROM @Keyword OPEN curKeyword FETCH NEXT FROM curKeyword into @Search WHILE (@@FETCH_STATUS = 0) BEGIN SET @SQL=@SQL+CHAR(13)+'     OR PATINDEX(''%'+@Search+'%'', Line) > 0' FETCH NEXT FROM curKeyword into @Search END -- while PRINT @SQL /* SELECT Result='FOUND' FROM [Text] WHERE 1 != 1 OR PATINDEX('%reliable%', Line) > 0 OR PATINDEX('%scalability%', Line) > 0 OR PATINDEX('%centralize%', Line) > 0 */ EXEC sp_executeSQL @SQL -- FOUND -- Cleanup DROP TABLE [Text] ------------ SQL Server Dynamic SQL & Dynamic SQL Stored Procedure links with more examples: http://www.sqlusa.com/bestpractices/dynamicsql/ The Curse and Blessings of Dynamic SQL How to search using all or partial columns with Dynamic SQL while avoiding SQL Injection
The Best SQL Server Training in the World