转自: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 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
*/ -- 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 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
------------ -- 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 |