|
Execute the following
Microsoft SQL Server T-SQL example scripts in Management Studio Query Editor to demonstrate the building and execution of dynamic SQL queries. The dynamic SQL query is executed in a child process.
------------
-- Dynamic SQL 101 - QUICK SYNTAX
------------
USE AdventureWorks2008;
EXEC ('SELECT * FROM Production.Product')
DECLARE @SQL varchar(256); SET @SQL='SELECT * FROM Production.Product'
EXEC (@SQL)
DECLARE @SQL varchar(256), @Table sysname;
SET @SQL='SELECT * FROM'; SET @Table = 'Production.Product'
SET @SQL = @SQL+' '+@Table
PRINT @SQL -- for debugging
EXEC (@SQL) -- Dynamic SQL execution
-- Dynamic SQL for rowcount in all tables
DECLARE @SQL nvarchar(max), @Schema sysname, @Table sysname;
SET @SQL = ''
SELECT @SQL = @SQL + '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 @SQL -- test & debug
EXEC sp_executesql @SQL
-- Equivalent code with the undocumented sp_MSforeachtable
EXEC sp_MSforeachtable 'select ''?'', count(*) from ?'
------------
-- FIRST EXAMPLE - dynamic SQL stored procedure for customer list
USE Northwind;
GO
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
-- Dynamic SQL stored procedure
CREATE PROCEDURE CustomerListByState
@States VARCHAR(128)
AS
BEGIN
DECLARE @SQL NVARCHAR(1024)
SET @SQL = 'select CustomerID, CompanyName, ContactName, Phone,
Region from Customers where Region
IN (' + @States + ')' + ' order by Region'
PRINT @SQL -- For testing and debugging
/* The following query is executed as dynamic SQL
select CustomerID, CompanyName, ContactName, Phone, Region
from Customers where Region IN ('WA', 'OR', 'ID', 'CA') order by Region
*/
-- Dynamic SQL execution
EXEC Sp_executesql @SQL
END
GO
-- Execute dynamic SQL stored procedure
DECLARE @States VARCHAR(100)
SET @States = '''WA'', ''OR'', ''ID'', ''CA'''
EXEC CustomerListByState @States
GO
/* Partial results
CustomerID CompanyName ContactName Phone Region
LETSS Let's Stop N Shop Jaime Yorres (415) 555-5938 CA
SAVEA Save-a-lot Markets Jose Pavarotti (208) 555-8097 ID
GREAL Great Lakes Food Market Howard Snyder (503) 555-7555 OR
HUNGC Hungry Coyote Import Store Yoshi Latimer (503) 555-6874 OR
*/
/* QUOTENAME can also be used to build sproc execution string
DECLARE @States VARCHAR(100)
SET @States = QUOTENAME('WA','''')+','+ QUOTENAME('OR','''')
EXEC CustomerListByState @States
GO
*/
------------
-- SECOND EXAMPLE - make TABLESAMPLE dynamic
-- Dynamic SQL for TABLESAMPLE
-- T-SQL dynamic sql
-- SQL tablesample
DECLARE @Size tinyint = 7
DECLARE @SQL nvarchar(512) =
'SELECT PurchaseOrderID, OrderDate = CAST(OrderDate AS DATE),
VendorID FROM AdventureWorks2008.Purchasing.PurchaseOrderHeader
TABLESAMPLE ('+ CAST(@Size AS VARCHAR)+' PERCENT)'
PRINT @SQL -- for testing & debugging purposes
/*
SELECT PurchaseOrderID, OrderDate = CAST(OrderDate AS DATE),
VendorID FROM AdventureWorks2008.Purchasing.PurchaseOrderHeader
TABLESAMPLE (7 PERCENT)
*/
-- SQL Server execute dynamic sql
EXEC sp_executesql @SQL
GO
-- (435 row(s) affected)
/* Partial results
PurchaseOrderID OrderDate VendorID
349 2003-06-23 1672
350 2003-06-23 1600
351 2003-06-23 1522
352 2003-06-23 1570
353 2003-06-23 1516
*/
------------
-- THIRD EXAMPLE - dynamic SQL execution with parameters
-- Dynamic SQL script - SQL Server 2008
-- Communicating with child process using command line parameters
-- sp_executesql parameter usage
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) -- For testing and debugging
/*
SELECT @LastFirstNameOUT = max(FirstName)
FROM Person.Person
WHERE left(LastName,1) = @FirstLetterOfLastName
*/
PRINT @ParmDefinition -- For testing and debugging
/*
@FirstLetterOfLastName char(1),
@LastFirstNameOUT nvarchar(50) OUTPUT
*/
EXECUTE sp_executeSQL
@SQL,
@ParmDefinition,
@FirstLetterOfLastName = @FirstLetter,
@LastFirstNameOUT=@LastFirstName OUTPUT
SELECT
[Last First Name] = @LastFirstName,
Legend='of last names starting with',
Letter=@FirstLetter
GO
------------
-- FOURTH EXAMPLE - enumerate all objects in databases
-- Return objects count in all databases on the server
-- Dynamic SQL stored procedure
-- SQL quotename used to build valid db object names
USE AdventureWorks;
GO
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = Object_id(N'[dbo].[sprocObjectCountsAllDBs]')
AND TYPE IN (N'P',N'PC'))
DROP PROCEDURE [dbo].[sprocObjectCountsAllDBs]
GO
CREATE PROC sprocObjectCountsAllDBs
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
-- SQL QUOTENAME is used for valid identifier formation
SET @SQL = 'select @dbObjects = count(*)' + Char(13) + 'from ' + Quotename(@dbName) + '.dbo.sysobjects'
PRINT @SQL -- Use it for debugging
-- Dynamic SQL call 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
END
GO
-- Execute stored procedure with dynamic SQL
EXEC sprocObjectCountsAllDBs
GO
------------
-- FIFTH EXAMPLE - automatic code generation for
-- datetime conversion from style 0 to 14
-- Dynamic SQL using temporary table
USE AdventureWorks2008;
DECLARE @I INT = -1
DECLARE @SQLDynamic nvarchar(1024)
CREATE TABLE #SQL(STYLE int, SQL varchar(256), Result varchar(32))
WHILE (@I < 14)
BEGIN
SET @I += 1
INSERT #SQL(STYLE, SQL)
SELECT @I, 'SELECT '+
'CONVERT(VARCHAR, GETDATE(), '+CONVERT(VARCHAR,@I)+')'
SET @SQLDynamic = 'UPDATE #SQL SET Result=(SELECT
CONVERT(VARCHAR, GETDATE(), '+CONVERT(VARCHAR,@I)+
')) WHERE STYLE='+ CONVERT(VARCHAR,@I)
PRINT @SQLDynamic
/* Printed in Messages - partial listing
UPDATE #SQL SET Result=(SELECT
CONVERT(VARCHAR, GETDATE(), 0)) WHERE STYLE=5
*/
EXEC sp_executeSQL @SQLDynamic
END
SELECT * FROM #SQL
DROP TABLE #SQL
------------
-- SIXTH EXAMPLE - Communicating between parent and
-- child process using a shared temporary table
-- Temporary table created in parent session is visible to child
-- Parent session
-- Dynamic SQL
DECLARE @SQL nvarchar(512)
SELECT City='Montreal', Country = convert(varchar(30),'Canada')
INTO #City
-- Child session T-SQL script
SET @SQL = 'SELECT * FROM #City;
INSERT #City VALUES(''Dallas'',''United States'');'
/* char(39)(ascii single quote) can be used to eliminate nested quotes
INSERT #City VALUES('+char(39)+'Dallas'+char(39)+','+char(39)+
'United States'+char(39)+');'
*/
PRINT @SQL
/*
SELECT * FROM #City; INSERT #City VALUES('Dallas','United States');
*/
EXEC sp_executeSQL @SQL
/* Result of select in child session
City Country
Montreal Canada
*/
SELECT * FROM #City
GO
/* Results
City Country
Dallas United States
Montreal Canada
*/
DROP TABLE #City
GO
------------
-- SEVENTH EXAMPLE - The importance of QUOTENAME usage
-- QUOTENAME ensures that dynamic SQL will 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
/*
select * from [Order Details]
*/
EXECUTE sp_executeSQL @SQL
END
GO
-- SQL execute script
-- SQL execute dynamic sql stored procedure
EXEC SelectFromAnyTable 'Order Details'
/* Partial results
OrderID ProductID UnitPrice Quantity Discount
10248 11 14.00 12 0
10248 42 9.80 10 0
10248 72 34.80 5 0
10249 14 18.60 9 0
*/
------------
-- EIGTH EXAMPLE - Dynamic pivot query for crosstabulation
-- SQL pivot crosstab report
-- SQL dynamic pivot
-- SQL dynamic sql
-- SQL dynamic crosstab report with pivot
USE AdventureWorks
GO
DECLARE @DynamicSQL AS NVARCHAR(MAX)
DECLARE @ReportColumnNames AS NVARCHAR(MAX)
-- SQL pivot list generation dynamically
-- Dynamic pivot list
SELECT @ReportColumnNames = Stuff( (
SELECT ', ' + QUOTENAME(YYYY) AS [text()]
FROM (SELECT DISTINCT YYYY=CAST (Year(OrderDate) as VARCHAR)
FROM Sales.SalesOrderHeader ) x
ORDER BY YYYY
-- SQL xml path for comma-limited list generation
FOR XML PATH ('')), 1, 1, '')
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
/*
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
*/
-- SQL dynamic query execution
EXEC sp_executesql @DynamicSQL
GO
/* Partial results
Store (Freight Summary) 2001 2002 2003 2004
Sundry Sporting Goods 1074.02 4609.31 4272.94 1569.04
Sunny Place Bikes 193.95 802.70 1095.83 411.62
Super Sports Store 102.15 743.51 427.80 301.68
Superb Sales and Repair 1063.69 1547.73 37.28 13.23
*/
------------
|
