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.