|
Execute the following Microsoft SQL Server T-SQL example scripts in Management Studio Query Editor to demonstrate dynamic SQL architecture with/without QUOTENAME, testing/debugging and execution.
-- Dynamic SQL quick syntax - dynamic sql SQL Server - mssql dynamic sql
DECLARE @SQL nvarchar(max), @Table sysname='AdventureWorks2008.Production.Product'
SELECT @SQL = 'SELECT Rows=count(*) FROM ' -- count rows dynamic query
SELECT @SQL = @SQL + @Table -- concatenate string variable
EXEC (@SQL) -- Original dynamic SQL execution command
-- 504
EXEC sp_executeSQL @SQL -- Improved ms dynamic SQL execute
-- 504
------------
-- SQL Server dynamic SQL query stored procedure quick syntax - dynamic T-SQL
USE AdventureWorks2008;
GO
CREATE PROCEDURE uspCountAnyKeyInAnyTable
@TableName SYSNAME,
@ColumnName SYSNAME,
@Wildcard NVARCHAR(64)
AS
DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = ' SELECT FrequencyCount=count(*) ' + ' FROM ' +
@TableName + ' WHERE ' + @ColumnName + ' LIKE ' +
CHAR(39)+@Wildcard + CHAR(39)
PRINT @SQL
EXEC sp_executesql @SQL
GO
EXECUTE uspCountAnyKeyInAnyTable 'Production.Product', 'Color', '%Blue%'
-- 26
------------
-- 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
------------
-- 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 ''?'''
------------
-- SQL Server dynamic PIVOT Query - T-SQL Dynamic Pivot Crosstab Report
------------
-- 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
*/
Important security article related to dynamic SQL:
How To: Protect From SQL Injection in ASP.NET
-- Dynamic SQL with in / out parameters:
-- Dynamic SQL execution with input / output parameters
-- SQL Server dynamic query - quotename
USE AdventureWorks2008;
DECLARE @ParmDefinition NVARCHAR(1024) = N'@FirstLetterOfLastName char(1),
@LastFirstNameOUT nvarchar(50) OUTPUT'
DECLARE @FirstLetter CHAR(1) = 'E', @LastFirstName NVARCHAR(50)
DECLARE @SQL NVARCHAR(MAX) = N'SELECT @LastFirstNameOUT = max(quotename(FirstName))
FROM Person.Person'+CHAR(13)+
'WHERE left(LastName,1) = @FirstLetterOfLastName'
PRINT @SQL+CHAR(13)
/*
SELECT @LastFirstNameOUT = max(quotename(FirstName))
FROM Person.Person
WHERE left(LastName,1) = @FirstLetterOfLastName
*/
PRINT @ParmDefinition
/*
@FirstLetterOfLastName char(1),
@LastFirstNameOUT nvarchar(50) OUTPUT
*/
-- Dynamic SQL with parameters, including OUTPUT parameter
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
[Xavier] of last names starting with E
*/
-- Dynamic SQL execution of OPENQUERY:
-- 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)
------------
-- Forming proper database object names :
-- T-SQL quotename
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
GO
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 & parametrized 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%'
*/
------------ |
