[转]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	(415) 555-5938
ID	SAVEA	Save-a-lot Markets	Jose Pavarotti	(208) 555-8097
OR	GREAL	Great Lakes Food Market	Howard Snyder	(503) 555-7555
OR	HUNGC	Hungry Coyote Import Store	Yoshi Latimer	(503) 555-6874
OR	LONEP	Lonesome Pine Restaurant	Fran Wilson	(503) 555-9573
OR	THEBI	The Big Cheese	Liz Nixon	(503) 555-3612
WA	LAZYK	Lazy K Kountry Store	John Steel	(509) 555-7969
WA	TRAIH	Trail's Head Gourmet Provisioners	Helvetius Nagy	(206) 555-8257
WA	WHITC	White Clover Markets	Karl Jablonski	(206) 555-4112

*/

-- 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