Ada Lovelace: Xây dựng , kiểm tra và thực thi dynamic sql .

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

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

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

/* 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;

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

CREATE PROCEDURE SelectFromAnyTable @table sysname

BEGIN

DECLARE @sql nvarchar(512)

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

PRINT @sql

EXECUTE sp_executeSQL @sql

END

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

/* 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;

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]

CREATE PROC sprocAllDBsSysobjectCounts

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

-- Test stored procedure - sproc

-- Execute stored procedure statement

EXEC sprocAllDBsSysobjectCounts

/* 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;

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

DELETE #IndexFragmentation

WHERE left(ObjectName,3) = 'sys'

ALTER TABLE #IndexFragmentation

ADD SchemaName SYSNAME NULL

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

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

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

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

CREATE PROCEDURE ListAllSprocsInfo

@SchemaPattern SYSNAME = NULL

-- set results to TEXT mode for execution

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

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

EXEC ListAllSprocsInfo 'Production'

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

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

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

/* Results

AdventureWorks2008 view count

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

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

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

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

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

begin

select LinesInserted = count(*) from inserted

end

'';

PRINT @nestedSQL

EXEC sp_executesql @nestedSQL;'

PRINT @SQL -- test & debug

EXEC sp_executesql @SQL

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

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

Source : sqlusa.cian

Ada Lovelace

Tuesday, January 27, 2015

Xây dựng , kiểm tra và thực thi dynamic sql .

No comments:

Post a Comment