datetime tune date into pad dynamic cursor money percent sp job isnumeric while over update
FREE TRIAL  SQL 2012 PROGRAMMING  SEARCH
SQL Server Scripts SQL 2005 SQL 2008 Articles
SQL JOBS NEWS FORMAT DEV JOBS
How to prepare, build, test & execute dynamic SQL?

The following Microsoft SQL Server T-SQL code example scripts demonstrate the flexibility and power of dynamic SQL architecture by building, testing/debugging and executing dynamic SQL queries and stored procedures including QUOTENAME usage. SQL Injection Attack Article: Data Security: Stop SQL Injection Attacks Before They Stop You

-- Simple Dynamic SQL Statement Execution - Dynamic SQL Tutorial
EXEC sp_executeSQL N'SELECT TOP(7) * FROM Northwind.dbo.Orders ORDER BY NEWID()'
------------ Dynamic SQL WHERE clause
DECLARE
@Predicate varchar(128) = 'ProductID=800'
EXEC ('SELECT * FROM AdventureWorks2008.Production.Product WHERE '+@Predicate)
------------ Dynamic view name / table name SELECT
DECLARE @SQL nvarchar(max), @View sysname='Northwind.dbo.Invoices'
SELECT @SQL = 'SELECT * FROM ' + @View
EXEC sp_executeSQL @SQL      -- SQL Server sp_executeSQL - Dynamic SQL in SQL Server
------------

-- Dynamic SQL sp_executeSQL usage with input & output parameters

DECLARE @SQL NVARCHAR(max), @ParmDefinition NVARCHAR(max)

DECLARE @ListPrice money =1700.99, @FirstProduct varchar(64)

SET @SQL =       N'SELECT @pFirstProduct = MIN(Name)

                   FROM AdventureWorks2008.Production.Product

                   WHERE ListPrice = @pListPrice'

SET @ParmDefinition = N'@pListPrice money,

                        @pFirstProduct varchar(64) OUTPUT'

 

EXECUTE sp_executeSQL   @SQL,

                        @ParmDefinition,

                        @pListPrice = @ListPrice,

                        @pFirstProduct=@FirstProduct OUTPUT

 

SELECT [ListPrice]=@ListPrice, FirstProduct=@FirstProduct

/*    ListPrice         FirstProduct

      1700.99           Road-350-W Yellow, 40 */

------------

-- Dynamic SQL - dynamic table name - dynamic sql SQL Server - sp_executeSQL

DECLARE @SQL nvarchar(max), @Table sysname='AdventureWorks2008.Production.Product'
SELECT @SQL = 'SELECT Rows=count(*) FROM '      -- count rows dynamic SQL statement
SELECT @SQL = @SQL + @Table                     -- concatenate string variables
EXEC (@SQL)                     -- Original dynamic SQL execution command
-- 504                          -- SQL execute dynamic SQL result
EXEC sp_executeSQL @SQL         -- Improved transact-SQL dynamic SQL execute
-- 504
------------

-- Dynamic SQL sort with collation - Dynamic ORDER BY - SQL Server 2008 code

DECLARE @SQL nvarchar(max)='SELECT Name, GroupName

  FROM AdventureWorks2008.HumanResources.Department

  ORDER BY Name '

DECLARE @Collation nvarchar(max) = 'COLLATE SQL_Latin1_General_CP1250_CS_AS'

SET @SQL=@SQL + @Collation

PRINT @SQL

EXEC sp_executeSQL @SQL

------------

-- SQL Server Concatenate, Test & Execute Dynamic SQL Statement

DECLARE @DynaSQL nvarchar(max)   -- String variable for assembly of SQL statement

DECLARE @ColumnList   nvarchar(max), @Where nvarchar(max), @Table nvarchar(max)

SET @ColumnList = 'SalesOrderID, OrderDate=convert(Date,OrderDate), Total=TotalDue'

SET @Table      = 'AdventureWorks2008.Sales.SalesOrderHeader'

SET @Where      = 'OrderDate = ''20040202'''

SET @DynaSQL = ' SELECT ' + @ColumnList + CHAR(13) + ' FROM '+@Table + CHAR(13) +

                    ' WHERE 1 = 1 AND ' + @Where

PRINT @DynaSQL

/* SELECT SalesOrderID, OrderDate=convert(Date,OrderDate), Total=TotalDue

   FROM AdventureWorks2008.Sales.SalesOrderHeader

   WHERE 1 = 1 AND OrderDate = '20040202'  */

EXECUTE sp_executeSQL @DynaSQL   -- T-SQL sp_executeSQL for dynamic SQL execution

-- (62 row(s) affected)

------------

Dynamic SQL script generates static (hardwired) T-SQL statement(s) at runtime. We can use the PRINT command to see the final SQL script and test it prior to execution.

-- SQL Server 2008 dynamic SQL query stored procedure - quick syntax - dynamic T-SQL
-- Dynamic SQL stored procedure SQL Server - Dynamic SQL in SQL Server
/***** DEMO ONLY - code is not secure against SQL Injection attack *****/
USE
AdventureWorks2008;
GO
CREATE PROCEDURE uspCountAnyKeyInAnyTable
               @TableName  SYSNAME,
               @ColumnName SYSNAME,
               @Wildcard   NVARCHAR(64)
AS
  DECLARE  @SQL  NVARCHAR(MAX)=' SELECT FrequencyCount=count(*) ' + ' FROM ' +
                @TableName + ' WHERE ' + @ColumnName + ' LIKE ' +
                CHAR(39)+@Wildcard + CHAR(39)
  PRINT @SQL -- for testing and debugging
  EXEC sp_executesql  @SQL
GO
EXECUTE uspCountAnyKeyInAnyTable 'Production.Product', 'Color', '%Blue%'
--  26
------------

Dynamic SQL is a powerful database programming technology that enables you to concatenate and execute T-SQL statements dynamically in a string variable at runtime. You can create robust data / parameter driven queries and stored procedures. For example, the PIVOT crosstab column list changes as the data grows, therefore it has to be built dynamically at runtime, and it cannot be hardwired. When rebuilding indexes in a cursor WHILE loop, the table name varies, it necessitates the use of dynamic SQL.
Static SQL stays the same in each execution. Dynamic SQL strings contain the text of a DML or DDL T-SQL script and can also contain placeholders for binding parameters. In the following example @pCountryCode is a placeholder for a parameter which is supplied at execution time.

------------
-- Example for declaring & passing parameter (@pCountryCode) to sp_executesql
------------

CREATE PROC sprocListStatesByCountry @CountryCode varchar(32) AS
DECLARE @SQL nvarchar(max)
DECLARE @Columns varchar(128) =                  'StateProvinceCode,CountryRegionCode,State=Name'
SET @SQL = 'SELECT ' + @Columns +
           ' FROM AdventureWorks2008.Person.StateProvince' +
           ' WHERE CountryRegionCode = @pCountryCode'
EXEC sp_executesql @SQL,
                   N'@pCountryCode nvarchar(32)',@pCountryCode = @CountryCode
GO
EXEC sprocListStatesByCountry 'DE'
-- (partial results) BY   DE    Bayern

------------

The QUOTENAME function returns a Unicode string with the square brackets added to make the input string a valid Microsoft SQL Server delimited identifier. SELECT QUOTENAME ('Order Details') returns [Order Details], a valid identifier with space. If an identifier is obtained from metadata, the use of QUOTENAME ensures validity.


-- T-SQL dynamic query example with QUOTENAME - count rows in all tables in database

USE
AdventureWorks2008;

DECLARE @DynamicSQL nvarchar(max) = '', @Schema sysname, @Table sysname;

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'

ORDER BY TABLE_SCHEMA, TABLE_NAME

PRINT @DynamicSQL -- test & debug

  /* .... ;SELECT '[Production].[ProductDescription]'= COUNT(*)

           FROM [Production].[ProductDescription]; ....   */

EXEC sp_executesql @DynamicSQL  -- sql server exec dynamic sql

-- Equivalent code with the undocumented sp_MSforeachtable - dynamic query

EXEC sp_MSforeachtable 'select ''?'', count(*) from AdventureWorks2008.?'

-- Related undocumented sp_MSforeachdb

EXEC sp_MSforeachdb 'select  ''?'''

The T-SQL PIVOT operator rotates rows into columns with the application an aggregate function such as SUM (crosstab). However, the PIVOT statement requires a static (hardwired) column list which on the other hand maybe data-driven, not known in advance, not known for the future, like YEARs for the columns which change with the data content of a database. The solution is dynamic SQL PIVOT with dynamic columns list preparation and usage in the PIVOT.

 

------------

-- SQL Server dynamic PIVOT Query - T-SQL Dynamic Pivot Crosstab - Dynamic Columns

------------

-- Unknown number of columns - Dynamic sql example - t sql dynamic query

USE AdventureWorks;

DECLARE @DynamicSQL AS NVARCHAR(MAX)

DECLARE @ReportColumnNames AS NVARCHAR(MAX)

-- SQL pivot list generation dynamically -  Dynamic pivot list - pivot dynamic

SELECT @ReportColumnNames = Stuff( (

SELECT ', ' + QUOTENAME(YYYY) AS [text()]

FROM (SELECT DISTINCT YYYY=CAST (Year(OrderDate) as VARCHAR)

      FROM Sales.SalesOrderHeader ) x

ORDER BY YYYY

FOR XML PATH ('')), 1, 1, '') -- SQL xml path for comma-limited list generation

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 - displays query prior to execution

-- SQL Server t sql execute dynamic sql

EXEC sp_executesql @DynamicSQL -- Execute dynamic SQL command

GO

/*  Partial results

Store (Freight Summary)       2001        2002        2003        2004

Neighborhood Store            NULL        2289.75     1120.64     NULL

New and Used Bicycles         1242.99     4594.51     4390.48     1671.98

*/

-- SQL injection dynamic SQL - protect from SQL injection attacks
Important security article related to dynamic SQL:
How To: Protect From SQL Injection in ASP.NET


-- Dynamic SQL with in / out parameters:

sp_executeSQL system stored procedure supports input and output parameter usage. With parameters, dynamic SQL execute statement resembles stored procedure execution with parameters. Readability, functionality and performance (query plan cached) are improved with parameters application.

 

-- Dynamic SQL execution with input / output parameters

-- SQL Server dynamic query - QUOTENAME - mssql dynamic sql

USE AdventureWorks2008;

DECLARE  @ParmDefinition NVARCHAR(1024) = N'@pFirstLetterOfLastName char(1),

      @pLastFirstNameOUT nvarchar(50) OUTPUT'

DECLARE @FirstLetter CHAR(1) = 'E', @LastFirstName NVARCHAR(50)

DECLARE @SQL NVARCHAR(MAX) = N'SELECT @pLastFirstNameOUT = max(QUOTENAME(FirstName))

      FROM Person.Person'+CHAR(13)+

      'WHERE left(LastName,1) = @pFirstLetterOfLastName'

PRINT @SQL+CHAR(13)

/*

SELECT @pLastFirstNameOUT = max(QUOTENAME(FirstName))

      FROM Person.Person

WHERE left(LastName,1) = @pFirstLetterOfLastName

*/

PRINT @ParmDefinition

/*

@pFirstLetterOfLastName char(1),

      @pLastFirstNameOUT nvarchar(50) OUTPUT

*/

-- Dynamic SQL with parameters, including OUTPUT parameter

EXECUTE sp_executeSQL

      @SQL,

      @ParmDefinition,

      @pFirstLetterOfLastName = @FirstLetter,

      @pLastFirstNameOUT=@LastFirstName OUTPUT

 

SELECT

      [Last First Name] = @LastFirstName,

      Legend='of last names starting with',

      Letter=@FirstLetter

GO

/* Results

Last First Name   Legend                        Letter

[Xavier]          of last names starting with   E

*/

SQL Server Dynamic SQL & Dynamic SQL Stored Procedure link with more examples:

http://www.sqlusa.com/bestpractices/dynamicsql/

-- Dynamic SQL execution of OPENQUERY:

Readability of dynamic SQL code is a challenge. The reason is that the building T-SQL code (concatenations) mingles with dynamic code. Formatting both codes is helpful. Using CHAR(39) for single quote reduces single quote sequence like ''''''.

 

-- OPENQUERY Dynamic SQL execution - SQL Server 2008 T-SQL code
DECLARE @DynamicSQL nvarchar(max) =
     'SELECT *
      FROM OPENQUERY(' + QUOTENAME(CONVERT(sysname, @@SERVERNAME))+ ',
      ''EXECUTE [AdventureWorks2008].[dbo].[uspGetWhereUsedProductID] 400,
      ''''2003-11-21'''''')'
PRINT @DynamicSQL
  /*
  SELECT *
  FROM OPENQUERY([YOURSERVER\SQL2008],
  'EXECUTE [AdventureWorks2008].[dbo].[uspGetWhereUsedProductID] 3,
  ''2003-12-01''')
  */
 
EXEC sp_executeSQL @DynamicSQL
-- (64 row(s) affected)
------------

 

A table name or column name may include space. If we do not enclose such a db object name in square brackets, the generated dynamic SQL string will be invalid. We can explicitly concatenate '[' and ']' or use the special function QUOTENAME.

-- Forming proper database object names :

-- T-SQL QUOTENAME function will add square bracket delimiters to system names

USE Adventureworks;

 

SELECT DatabaseObject = QUOTENAME(table_schema) + '.' + QUOTENAME(table_name),

       t.*

FROM   INFORMATION_SCHEMA.TABLES t

WHERE  table_type IN ('VIEW','BASE TABLE')

       AND Objectproperty(Object_id(QUOTENAME(table_schema) + '.' +

           QUOTENAME(table_name)), 'IsMSShipped') = 0

GO

/* Partial results

 

DatabaseObject

[Production].[ProductProductPhoto]

[Sales].[StoreContact]

[Person].[Address]

[Production].[ProductReview]

[Production].[TransactionHistory]

*/

-- Forming proper database object names within a
-- dynamic SQL query stored procedure with QUOTENAME:

-- QUOTENAME will make dynamic SQL execute correctly

USE Northwind;

GO

-- SQL create stored procedure - SQL dynamic SQL - SQL QUOTENAME usage

CREATE PROCEDURE SelectFromAnyTable @table sysname

AS

BEGIN

      DECLARE @sql nvarchar(512)

      SET @sql = 'select * from '+ QUOTENAME(@table)

      PRINT @sql

      EXECUTE sp_executeSQL @sql

END

GO 

-- SQL execute script - SQL execute dynamic sql stored procedure

exec SelectFromAnyTable 'Order Details'

/* Messages 

select * from [Order Details]

(2155 row(s) affected)

*/

 

-- Forming Year column header in dynamic crosstab query:

 

-- SQL pivot crosstab report - SQL QUOTENAME - SQL dynamic pivot

-- SQL dynamic crosstab report with pivot - SQL dynamic sql

USE AdventureWorks;

DECLARE  @OrderYear  AS  TABLE( YYYY INT    NOT NULL    PRIMARY KEY)

DECLARE  @DynamicSQL  AS NVARCHAR(4000)

 

INSERT INTO @OrderYear

SELECT DISTINCT Year(OrderDate)

FROM   Sales.SalesOrderHeader

 

DECLARE  @ReportColumnNames  AS NVARCHAR(MAX),

         @IterationYear      AS INT

 

SET @IterationYear = (SELECT Min(YYYY) FROM   @OrderYear)

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

                          WHERE  YYYY > @IterationYear)

  END

 

SET @ReportColumnNames = Substring(@ReportColumnNames,2,Len(@ReportColumnNames))

 

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

-- SQL QUOTENAME placed the square brackets around the year (YYYY)

 

/*

SELECT * FROM (SELECT [Store (Freight Summary)]=s.Name, 

YEAR(OrderDate) AS OrderYear,  Freight = convert(money,convert(varchar, Freight)) 

FROM Sales.SalesOrderHeader soh  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 query

EXEC Sp_executesql   @DynamicSQL

GO

/* Partial results

 

Store (Freight Summary)             2001        2002        2003        2004

Grease and Oil Products Company     104.02      555.02      726.75     272.28

Great Bicycle Supply                4430.26     3871.35     NULL        NULL

Great Bikes                         1653.89     7445.16     7525.98    584.63

Greater Bike Store                  489.79      1454.78     864.08     245.22

*/

 

-- Forming database name in dynamic SQL:

 

-- Return objects count in all databases on the server - SQL Server dynamic SQL

-- SQL Server QUOTENAME - SQL stored procedure - SQL dynamic query

USE AdventureWorks;

GO

IF EXISTS (SELECT *

           FROM   sys.objects

           WHERE  object_id = OBJECT_ID(N'[dbo].[sprocAllDBsSysobjectCounts]')

                  AND TYPE IN (N'P',N'PC'))

  DROP PROCEDURE [dbo].[sprocAllDBsSysobjectCounts]

GO

 

CREATE PROC sprocAllDBsSysobjectCounts

AS

  BEGIN

      SET NOCOUNT ON

    DECLARE  @dbName      SYSNAME,

             @ObjectCount INT

    DECLARE  @DynamicSQL NVARCHAR(MAX)

-- SQL Server table variable

    DECLARE  @DBObjectStats  TABLE(

                                   DBName    SYSNAME,

                                   DBObjects INT

                                   )

-- SQL Server cursor

    DECLARE curAllDBs CURSOR  FOR

    SELECT   name

    FROM     MASTER.dbo.sysdatabases

-- SQL NOT IN set operator - exclude system db-s

    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 @DynamicSQL = 'select @dbObjects = count(*)' + char(13) + 'from ' +

         -- SQL QUOTENAME

         QUOTENAME(@dbName) + '.dbo.sysobjects'

        PRINT @DynamicSQL  -- Use it for debugging

/* Partial listing

 

select @dbObjects = count(*)

from [AdventureWorks].dbo.sysobjects

*/

        -- Dynamic sql call with output parameter(s)

        EXEC sp_executesql

          @DynamicSQL,

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

GO

 

-- Test stored procedure - sproc

-- Execute stored procedure statement

EXEC sprocAllDBsSysobjectCounts

GO

/* Partial results

 

DBName            DBObjects

AdventureWorks    748

AdventureWorks3NF 749

AdventureWorksDW  169

AdvWorksDWX       137

Audit             48

*/

 

-- Forming schema & table id in dynamic SQL:

 

------------

-- SQL Server dbreindex fragmented indexes - using cursor, QUOTENAME & dynamic SQL

-- SQL Server BUILD indexes

-- Reindex all indexes over 35% logical fragmentation with 90% fillfactor

------------

USE AdventureWorks2008;

GO

-- Create temporary table to hold meta data information about indexes

CREATE TABLE #IndexFragmentation (

  ObjectName     CHAR(255),         ObjectId       INT,

  IndexName      CHAR(255),         IndexId        INT,

  Lvl            INT,               CountPages     INT,

  CountRows      INT,               MinRecSize     INT,

  MaxRecSize     INT,               AvgRecSize     INT,

  ForRecCount    INT,               Extents        INT,

  ExtentSwitches INT,               AvgFreeBytes   INT,

  AvgPageDensity INT,               ScanDensity    DECIMAL,

  BestCount      INT,               ActualCount    INT,

  LogicalFrag    DECIMAL,           ExtentFrag     DECIMAL)

 

INSERT #IndexFragmentation

EXEC( 'DBCC SHOWCONTIG WITH TABLERESULTS , ALL_INDEXES')

 

GO

 

DELETE #IndexFragmentation

WHERE  left(ObjectName,3) = 'sys'

GO

 

ALTER TABLE #IndexFragmentation

ADD SchemaName SYSNAME    NULL

GO

 

UPDATE [if]

SET    SchemaName = SCHEMA_NAME(schema_id)

FROM   #IndexFragmentation [if]

       INNER JOIN sys.objects o

         ON [if].ObjectName = o.name

WHERE  o.TYPE = 'U'

 

-- select * from #IndexFragmentation 

-- SQL cursor 

-- SQL dynamic SQL 

-- SQL while loop

DECLARE  @MaxFragmentation DECIMAL = 35.0

 

DECLARE  @Schema     SYSNAME,

         @Table      SYSNAME,

         @DynamicSQL NVARCHAR(512)

DECLARE  @objectid INT,

         @indexid  INT

DECLARE  @Fragmentation    DECIMAL

-- T-SQL cursor declaration

DECLARE curIndexFrag CURSOR  FOR

SELECT   SchemaName,

         ObjectName,

         LogicalFrag = max(LogicalFrag)

FROM     #IndexFragmentation

WHERE    LogicalFrag >= @MaxFragmentation

         AND indexid != 0

         AND indexid != 255

GROUP BY SchemaName,  ObjectName

 

OPEN curIndexFrag

FETCH NEXT FROM curIndexFrag

INTO @Schema,

     @Table,

     @Fragmentation

 

WHILE @@FETCH_STATUS = 0

  BEGIN

    -- T-SQL QUOTENAME

    SELECT @DynamicSQL = 'DBCC DBREINDEX (''' +

    QUOTENAME(RTRIM(@Schema)) + '.' + QUOTENAME(RTRIM(@Table)) + ''', '''', 90)'

    

    PRINT @DynamicSQL -- debug & test

-- Dynamic sql execution    

    EXEC( @DynamicSQL)

-- Alternate (new way): EXEC sp_executeSQL @DynamicSQL

    

    FETCH NEXT FROM curIndexFrag

    INTO @Schema, @Table, @Fragmentation

  END

CLOSE curIndexFrag

DEALLOCATE curIndexFrag

GO

 

/* Partial messages

 

DBCC DBREINDEX ('[Person].[StateProvince]', '', 90)

DBCC execution completed. If DBCC printed error messages, 

contact your system administrator.

DBCC DBREINDEX ('[Sales].[Store]', '', 90)

DBCC execution completed. If DBCC printed error messages, 

contact your system administrator.

DBCC DBREINDEX ('[Purchasing].[Vendor]', '', 90)

DBCC execution completed. If DBCC printed error messages, 

contact your system administrator.

*/

 

-- Cleanup

DROP TABLE #IndexFragmentation

GO

------------

 

-- Forming schema & sproc id in dynamic SQL:

 

------------

-- SQL Server T-SQL script generator dynamic SQL stored procedure

------------

-- SQL sproc dynamic parameter - when omitted, ALL is selected

-- SQL QUOTENAME

SET nocount  ON

USE AdventureWorks;

GO

CREATE PROCEDURE ListAllSprocsInfo

                @SchemaPattern SYSNAME  = NULL

-- set results to TEXT mode for execution

AS

  BEGIN

    SET nocount  ON

    

    DECLARE  @DynamicSQL NVARCHAR(4000)

    

    SET @DynamicSQL='SELECT ''EXEC sp_help '' +''''''''+QUOTENAME(ROUTINE_SCHEMA) +

            ''.''  + QUOTENAME(ROUTINE_NAME) +'''''''' + CHAR(13)

            FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_TYPE =''PROCEDURE'''

    

    IF @SchemaPattern IS NOT NULL

      SET @DynamicSQL = @DynamicSQL + N' AND ROUTINE_SCHEMA LIKE ''' +

            @SchemaPattern + ''''

    

    -- print @DynamicSQL -- test & debug

    EXEC sp_executeSQL @DynamicSQL

    -- EXECUTE( @DynamicSQL) -- old way

  END

 

GO

 

-- Execute stored procedure

-- Set Query Results to Text in Management Studio Query Editor

-- Copy and paste results to new query window for execution

EXEC ListAllSprocsInfo

GO

 

EXEC ListAllSprocsInfo   'Production'

GO

/* Partial results

 

EXEC sp_help '[dbo].[uspPrintError]'

 

EXEC sp_help '[dbo].[sprocPingLinkedServer]'

 

EXEC sp_help '[dbo].[uspLogError]'

*/

 

------------

 

-- Forming database name in dynamic SQL sproc:

 

------------

-- MSSQL assign table count in database to variable - QUOTENAME function

------------

-- Microsoft T-SQL dynamic sql stored procedure with input / output parameters

ALTER PROCEDURE uspViewCount

                @DatabaseName SYSNAME,

                @Tables       INT  OUTPUT

AS

  BEGIN

    DECLARE  @DynamicSQL NVARCHAR(256), @Count INT

    SET @DynamicSQL = N'SELECT @Count = COUNT(*) FROM ' +

                              QUOTENAME(@DatabaseName) +

                     '.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE=''VIEW'''

    PRINT @DynamicSQL  -- Debug & test

 /* SELECT @Count = COUNT(*) FROM [AdventureWorks2008].INFORMATION_SCHEMA.TABLES

    WHERE TABLE_TYPE='VIEW' */

 

    -- Dynamic SQL execution with output parameters

    EXEC sp_executesql

      @Query = @DynamicSQL ,

      @Params = N'@Count INT OUTPUT' ,

      @Count = @Count OUTPUT

    SET @Tables = @Count

  END

GO

 

-- Microsoft SQL Server T-SQL execute stored procedure

-- SQL Assign sproc result to variable

DECLARE  @AWtables INT

EXEC uspViewCount  'AdventureWorks2008' ,   @AWtables OUTPUT

SELECT 'AdventureWorks2008 view count' = @AWtables

GO

/* Results

 

AdventureWorks2008 view count

20

*/

------------

 

-- Forming servername in dynamic SQL sproc:

 

------------

-- Find where ProductID=3 is being used by select into from sproc execution

------------

-- SQL select into table create from sproc

-- T-SQL dynamic SQL OPENQUERY

/* 

DATA ACCESS to current instance can be setup the following way

 

exec sp_serveroption @server = 'PRODSVR\SQL2008'

      ,@optname = 'DATA ACCESS'

      ,@optvalue = 'TRUE'

 

This way, OPENQUERY can be used against current instance

(Usually OPENQUERY is used to access linked server.)     

*/

DECLARE @sqlQuery nvarchar(max) =

      'SELECT *

       INTO   BikePartsInAssembly

       FROM   OPENQUERY(' + QUOTENAME(CONVERT(sysname, @@SERVERNAME))+ ',

                ''EXECUTE [AdventureWorks2008].[dbo].[uspGetWhereUsedProductID]  3,

            ''''2003-12-01'''''')'

PRINT @sqlQuery

/*

SELECT *

       INTO   BikePartsInAssembly

       FROM   OPENQUERY([PRODSVR\SQL2008],

                'EXECUTE [AdventureWorks2008].[dbo].[uspGetWhereUsedProductID]  3,

            ''2003-12-01''')

*/

 

EXEC sp_executeSQL @sqlQuery

 

SELECT   TOP ( 5 ) *

FROM    BikePartsInAssembly

ORDER BY NEWID()

GO

 

/* Partial results

 

ProductAssemblyID ComponentID       ComponentDesc

966               996               Touring-1000 Blue, 46

762               994               Road-650 Red, 44

956               996               Touring-1000 Yellow, 54

994               3                 LL Bottom Bracket

983               994               Mountain-400-W Silver, 46

*/

-- Cleanup

DROP TABLE BikePartsInAssembly

GO

------------

 

-- Changing database context in dynamic SQL:

 

------------

-- Nested dynamic SQL - change database context - USE usage

------------

USE AdventureWorks2008;

DECLARE @SQL nvarchar(max)='use Northwind;

DECLARE @nestedSQL nvarchar(max)= ''create trigger trgEmpInsert

  on Employees

  for insert

  as

  begin

    select LinesInserted = count(*) from inserted

  end

'';

PRINT @nestedSQL

EXEC sp_executesql @nestedSQL;'

PRINT @SQL -- test & debug

EXEC sp_executesql @SQL

GO

 

-- Cleanup

USE Northwind; DROP TRIGGER trgEmpInsert;

------------

 

-- Default & parameterized use of QUOTENAME:

 

/* Typically square brackets (in dynamic SQL queries), single quotes and double quotes are used with QUOTENAME. */

 

-- SQL QUOTENAME default usage - SQL brackets - SQL square brackets

select QUOTENAME('Factory Order Detail')

-- Result: [Factory Order Detail]

 

-- SQL QUOTENAME equivalent use - MSSQL server quote name - bracketing name

select QUOTENAME('Factory Order Detail','[]')

-- Result: [Factory Order Detail]

 

-- The second argument is: single quote, double quote, single quote

select QUOTENAME('Factory Order Detail','"')

-- Result: "Factory Order Detail"

 

select QUOTENAME('Factory Order Detail','()')

-- Result: (Factory Order Detail)

-- Search wildcard preparation to be used in dynamic SQL text search

DECLARE @SearchKeyword nvarchar(32) = 'O''Reilly'

DECLARE @SearchWildcard nvarchar(32) =

        QUOTENAME('%' + @SearchKeyword + '%',CHAR(39)+CHAR(39))

PRINT @SearchKeyword

PRINT @SearchWildcard

/*

O'Reilly

'%O''Reilly%'

*/

------------

Related article:

http://www.sqlusa.com/bestpractices/dynamicsql/

Dynamic SQL link with theory:

The Curse and Blessings of Dynamic SQL

 

Exam Prep 70-461
Exam 70-461