sp_get_query_template

Systémová procedura sp_get_query_template je nenápadný, ale velmi užitečný pomocník. Primárně je sice určena pro vytváření šablon dotazu pro plan guidy, ale stejně tak dobře ji můžeme využít pro scénář, kdy máme rozsáhlé query s řadou parametrů a z nějakého důvodu potřebujeme dotaz volat z dynamického sql, např. pro různé databáze nebo linkované servery. Procedura pracuje podle pravidel forced parametrizace, tzn. umožňuje parametrizovat i dotazy s triviálními exekučními plány.

Proceduru voláme podle následující šablony:

sp_get_query_template
   [ @querytext = ] N'query_text'
   , @templatetext OUTPUT 
   , @parameters OUTPUT

• @query text – dotaz, pro který chceme vygenerovat šablonu. Musí být uzavřen do jednoduchých uvozovek a označen jako unicode (N”).
• @templatetext – vrací text parametrizovaného dotaz
• @parameters – vrací seznam parametrů s datovými typy

Jak to vše funguje v praxi si ukážeme v tomto jednoduchém příkladu:

DECLARE @TemplateText NVARCHAR(MAX)
DECLARE @Parameters NVARCHAR(MAX)
 
EXEC sp_get_query_template
 N'SELECT *
 FROM [dbo].[TestTable]
 WHERE Id IN (5, 6) OR 
 Date BETWEEN GetDATE() AND ''2010-01-01'' OR
 Date = DATEADD(day, 1, GETDATE()) OR
 Date = GETDATE()',
 @templatetext OUTPUT,
 @parameters OUTPUT;

SELECT @TemplateText AS TemplateText;
SELECT @Parameters AS Parameters
GO

Bohužel i zde narazíme na drobná omezení, stačí se podívat na vygenerovanou šablonu v Management Studiu:

select * 
from [dbo] . [TestTable] 
where Id in ( @0 , @1 ) or 
	  Date between GetDATE ( ) and @2 or 
	  Date = DATEADD ( day , @3 , GETDATE ( ) ) or 
	  Date = GETDATE ( )

Vidíme, že v textu se vyskytují mezery mezi znaky, což představuje problém pro dynamické sql a bez dalšího formátování se neobejdeme. Pozornost je třeba věnovat i datovým typům parametrů, protože procedura sice správně pozná INT datové typy, ale pokud bude sloupec [Date] v tabulce dbo.TestTable datového typu DATE nebo DATETIME, dostaneme stejně parametr @2 jako varchar(8000) řetězec.