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.