V minulém článku jsem popsal jak nepsat dynamické SQL. Dneska ukážu jak to udělat správně. Rovnou začnu přepsanou procedurou z minulého článku.

CREATE OR ALTER PROCEDURE p_CiselnikDejNazevDleKodu
    @Schema SYSNAME = 'dbo',
    @Tabulka SYSNAME,
    @Kod NVARCHAR(50),
	@Debug BIT = 0
AS
BEGIN
    DECLARE @prikaz NVARCHAR(MAX)
    SET @prikaz = N'
SELECT TOP (1) nazev 
FROM ' + QUOTENAME(@Schema) + '.' + QUOTENAME(@Tabulka) + N' 
WHERE kod = @Kod';

	IF @Debug = 1
		PRINT(@prikaz);

    EXEC sys.sp_executesql
		@stmt = @prikaz,
		@param = N'@Kod NVARCHAR(50)',
		@Kod = @Kod
END
GO

Na vstupu procedury mi chybělo schéma, které jsem tady doplnil s výchozí hodnotou dbo. Schéma a název tabulky musím do příkazu zřetězit. Obojí ošetřuji funkcí QUTENAME, která názvy obalí hranatými závorkami a zdvojí případný výskyt pravé hranaté závorky a tím zabrání SQL injekci. Funkce QUTENAME by se měla používat na všechny systémové názvy, to je databáze, tabulky, sloupce, atd.. Co kdyby nějaký filuta pojmenoval tabulku [;DROP TABLE Students--], nebo tak nějak.
Abych ošetřil i parametr @Kod, tak místo zřetězení jeho hodnoty ho zadávám jako parametr @Kod rovnou do řetězce příkazu. Volání EXEC(@prikaz) jsem nahradil voláním EXEC sys.sp_executesql. Tomu předávám samotný příkaz, pak seznam parametrů a jejich datových typů, které jsou v příkazu použity a nakonec parametrům předám hodnotu. Příkaz PRINT jsem podmínil parametrem @Debug, aby se neprintovalo vždy a zbytečně.

Ještě kouknem, jak to vypadá v plan cache.

DBCC FREEPROCCACHE
 
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'Obor',  @Kod = N'P'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'Obor',  @Kod = N'E'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'TypObjektu',  @Kod = N'CH'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'TypObjektu',  @Kod = N'D'
 
SELECT
    p.usecounts, 
    p.cacheobjtype, 
    p.objtype, 
    p.size_in_bytes,
    t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
WHERE p.cacheobjtype = 'Compiled Plan'
AND t.text NOT LIKE '%dm_exec_cached_plans%';
GO

V plan cache mám tentokrát dva prepared plány a každý z toho dvakrát použitý. Plán se tedy pro každou tabulku sestaví jednou a pak se používá opakovaně i pro různé hodnoty parametru @Kod. Minule se mi pro každou hodnotu kódu sestavoval nový adhoc plan.

Mějme nějaké číselníkové tabulky s daty:

CREATE TABLE dbo.Obor(
	IdObor INT IDENTITY NOT NULL 
		CONSTRAINT pk_Obor PRIMARY KEY CLUSTERED,
	Kod NVARCHAR(50) NOT NULL,
	Nazev NVARCHAR(250) NOT NULL
)
GO

INSERT INTO dbo.Obor(Kod, Nazev)
VALUES
(   N'P', N'Plyn'),
(   N'E', N'Elektřina')
GO

CREATE TABLE dbo.TypObjektu(
	IdTypObjektu INT IDENTITY NOT NULL 
		CONSTRAINT pk_TypObjektu PRIMARY KEY CLUSTERED,
	Kod NVARCHAR(50) NOT NULL,
	Nazev NVARCHAR(250) NOT NULL
)
GO

INSERT INTO dbo.TypObjektu(Kod, Nazev)
VALUES
(   N'CH', N'Chata'),
(   N'D', N'Dům')
GO

A mějme výborný nápad na proceduru, která vrátí název ke kódu číselníku a libovolné tabulce.

CREATE OR ALTER PROCEDURE p_CiselnikDejNazevDleKodu
	@Tabulka SYSNAME,
	@Kod NVARCHAR(50)
AS
BEGIN
	DECLARE @prikaz NVARCHAR(MAX)
	SET @prikaz = N'
SELECT TOP (1) nazev 
FROM ' + @Tabulka + N' 
WHERE kod = ''' + @Kod + N'''';
	PRINT(@prikaz);
	EXEC(@prikaz);
END
GO

Vypadá to, že to funguje parádně.

EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'Obor',  @Kod = N'P'
/*
Vrátí:
nazev
-----
Plyn
*/

Ale pokud vyliji plan cache, parkát to spustím a kouknu co je v keši:

DBCC FREEPROCCACHE

EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'Obor',  @Kod = N'P'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'Obor',  @Kod = N'E'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'TypObjektu',  @Kod = N'CH'
EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'TypObjektu',  @Kod = N'D'

SELECT 
	p.usecounts, 
	p.cacheobjtype, 
	p.objtype, 
	p.size_in_bytes,
	t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
WHERE p.cacheobjtype = 'Compiled Plan'
AND t.text NOT LIKE '%dm_exec_cached_plans%';
GO

Teď už to nevypadá tak dobře. Každý select spálil CPU na výpočet plánu, každý text selectu a jeho plán je v plan cache a zabíra tam zbytečně místo. Nemusí jít jen o číselníky. Dneska jsem viděl tisíce položek v keši, které se lišily jen v id, které bylo zřetězené do dotazu.
Samotný dotaz jede rychle, ale pálí se zbytečně CPU na výpočet plánu a zabírá se paměť. Tím jde dolů výkon celého serveru.

A co když je některý parametr procedury vstupem od uživatele?

EXEC p_CiselnikDejNazevDleKodu @Tabulka = N'nevim',  @Kod = N'P'
/*
SELECT TOP (1) nazev 
FROM nevim 
WHERE kod = 'P'
Msg 208, Level 16, State 1, Line 60
Invalid object name 'nevim'.
*/

Pokud se neošetřené výjimky vracejí až k uživateli, tak díky printu dostane celý text, co je v komentáři výše. Prostě včetně struktury toho pochybného SELECT TOP (1)…

No a pokud bude uživatel důvtipnější, tak si vrátí strukturu celé DB.

EXEC p_CiselnikDejNazevDleKodu 
    @Tabulka = N'Obor',  
    @Kod = N''' UNION SELECT name FROM sys.tables--'
/*
nazev
------------
a
Obor
tabule
tabule2
TypObjektu
*/

Prostě Little Bobby Tables.

Pokračování článku o tom, jak by to mělo vypadat.