technika

INSERT EXEC

An INSERT EXEC statement cannot be nested. T-SQL chyba číslo 8164, která mě dokáže vytočit. proč v MS SQL Serveru tohle trapné omezení je ? odpověď na tuto záludnou otázku znám, ale neobešla by se bez hodně sprostých slov (např. "sráči" je v ní minilámně 4x) a tak raději zkusím odpovědět na otázku jinou – dá se to nějak obejít ? čtenáři, kteří se o podrobnosti nezajímají, mohou skočit rovnou na konec článku. jedno univerzální řešení tam je.

o co přesně jde ? v T-SQL existuje užitečná kombinace příkazů INSERT a EXEC, která umožní vložit data z uložené procedury do tabulky. data pak lze dále zpracovávat přímo na serveru, což jinak s výstupem z uložené procedury dělat nelze. problém je ale v tom, že v rámci jednoho volání procedury lze tuto vymoženost použít jen jednou. pokud se v proceduře, která je volána pomocí INSERT EXEC, vyskutuje jiný INSERT EXEC, zahlásí SQL server tuto milou zprávu:

Msg 8164, Level 16, State 1, Procedure PPP, Line XXX
An INSERT EXEC statement cannot be nested.

tak tedy – dá se to nějak obejít ? existují dvě skupiny řešení. řešení z té první skupiny vyžadují přepsání původního kódu uložené procedury – jinými slovy, vyhnout se kombinaci INSERT EXEC. jednou z možností je přepsat proceduru, z které data primárně pořizujeme, na funkci vracející tabulku. místo kombinace INSERT EXEC pak lze použít bezproblémové INSERT SELECT:

insert  into tabulka
select  *
from    dbo.moje_funkce(parametr1, parametr2, ...)

funkce má však oproti proceduře hromadu omezení a tak toto řešení není zrovna univerzální. je ale poměrně čisté a tak se může někdy hodit. další možností je neposílat data z uložené procedury na výstup, ale uložit je do nějaké tabulky – třeba temporární a ani nemusí být globální (lokální tabulka je vidět v rámci jedné session a volání procedur, byť vnořené, v jedné session probíhá). pak probíhá volání nějak takhle:

create  #tabulka (col1 int, col2 varchar(128))
exec    ulozena_proceruda parametr1, parametr2, ...
-- v procedure je realizovano plneni tabulky #tabulka napr. pomoci INSERT

tohle je už řešení univerzálnější, ale zase není tak elegantní a vyžaduje práva pro zakládání temporárních tabulek. řešení lze i modifikovat na použítí běžné, ne temporární tabulky, ale pak je třeba řešit multiuser provoz a v tabulce si označovat, která data patří ke kterému volání.

další možností je použít výstupní parametry procedury. jelikož se bavíme o procedurách, které vracejí tabulku, jde o větší objem dat a pochopitelně nemá smysl rozšiřovat proceduru o dalších 7845 parametrů, nehledě na to, že počet parametrů procedury je omezen na 2100. samozřejmě, data lze narvat do jednoho stringu nebo binárky, ale to není moc elegantní a taky se s tím nepracuje nejlépe (i když ukládání do binární proměnné už jsem jednou použil, ale v jiném kontextu). zbývají tedy jen dva typy – cursor a XML. použití je jasné – po zavolání procedury otevřu nějakým cursorem data a ty pak načtu, nebo si data předám pomocí XML struktury. případ cursor by mohl vypadat asi takhle:

create procedure procedura
@parametr int,
@kurzor   cursor varying output
as
set @kurzor = cursor local for select * from type_instr
open    @kurzor
go

-- volani je nasledujici
declare @kurzor cursor
exec    procedura parametr1, @kurzor output
fetch next from @kurzor into ...
while @@fetch_status begin
 ...
end
close @kurzor

parametr @kurzor musí být deklarován jako varying (význam viz. MS help, přeji pěkné počtení) a samozřejmě jako output a musí být otevřen už ve volané proceduře. volající ho pak jen projede řádek po řádku a zavře. nevýhoda je jasná – nutnost data prohánět smyčkou a zpracovávat každý řádek zvlášť. použít XML strukturu je o něco pohodlnější:

create procedure procedura
@parametr int,
@data     XML output
as
select  @data = (select sloupec1, sloupec2, ... from tabulka for XML raw('data'))
go

-- volani je nasledujici
declare @data XML
exec    procedura parametr1, @data output
select  c.value('@sloupec1', 'varchar(32)'),
        c.value('@sloupec2', 'int'),
        ...
from    @data.nodes('/data') as t(c)

řešení z první skupiny jsou vyčerpána, tak hurá na skupinu drouhou. ta zahrnuje řešení univerzální, která se obejdou bez modifikace uložené procedury. kombinace INSERT EXEC v ní klidně může zůstat. prvním řešením je napsat si v .NOTu (moje přezdívka pro .NET) CLR proceduru. ta bude velice jednoduchá – prostě zavoláme naší proceduru, která používá INSERT EXEC, data uchováme v nějakém vhodném datasetu a ten pak vrátíme jako výstup CLR procedury. tento výstup už jde dále pomocí INSERT EXEC zpracovávat. tenhle článek má být ale primárně o T-SQL a tak je tu ještě druhé řešení – použití OPENROWSET:

declare @openr varchar(max)

set     @openr = 'exec procedura '+ parametr1 + ', ' + parametr2 + ', ' + …
set     @openr = 'select * from openrowset(''SQLNCLI'',
                 ''SERVER=muj_server;DATABASE=moje_databaze;Trusted_Connection=Yes'',
                 ''' + replace(@openr, '''', '''''') + ''')'

insert  into tabulka
exec    (@openr)

OPENROWSET totiž spustí dotaz externě – pro SQL server se tváří jako dotaz z venku. tím se volaní oddělí, nedojde k vnoření a tudíž ani k vyvolání chyby 8164. samotný OPENROWSET ale řešením není. neumožňuje totiž jako parametry používat proměnné, musí být zapsány jako text, součást zdrojového kódu. je tedy nutné si nejdříve select s OPENROWSETem sestavit. abych nemusel přemýšlet nad počtem apostrofů, uložím do proměnné nejdříve T-SQL příkaz pro spuštění procedury a pak následně sestavím T-SQL příkaz pro OPENROWSET, v němž pomocí replace všechny apostrofy zdvojím. jde vlastně o T-SQL ve stringu obsahující T-SQL ve stringu, takže zdvojování je nutné (+ další zdvojování na úrovni zdrojáku, to je ale legrace).

nezbývá mi než připomenout, že toto řešení je neskutečná prasárna a vlastně by se nemělo vůbec používat. jenže… co když už nic jiného nezbývá ?