====== Techniky T-SQL ======
Transact-SQL (T-SQL) je proprietární rozšíření pro SQL od společností Microsoft a Sybase. Microsoft tento jazyk pužívá ve svých produktech Microsoft SQL Server . T-SQL poskytuje širokou škálu funkcionality pro programáory nebo databázové administrátory. Transact SQL umožňuje řízení SQL dotazů, podmíněné zpracování konstrukcemi IF, ELSE, CASE, nebo WHILE, využití zabudovaných funkcí MSSQL serveru, nebo také sekvenční zpracování pomocí kurzorů. Velká síla T-SQL je v uložených procedurách díky nimž mohou programátoři definovat dávky SQL a znovuvyužívat kód, definovat triggery, nebo například řešit část aplikační logiky už na databázovém serveru. Administrátoři moho pomocí systémových uložených procedur administrovat server, sytémové procedury totiž umožňují pomocí T-SQL nastavovat velkou škálu parametrů SQL serveru, vytvářet databáze, zálohovat, nastavovat práva atd... 

===== Podmíněné zpracování =====
  * T-SQL umožňuje vracet hodnoty dotazu na základě dalšího zpracování a logického vyhodnocení výrazů. Můžeme vykonat blok příkazů pokud je splněna podmínka, nebo vykonat úplně jiný kód pokud podmínka splněna není. Na základě získaný hodnot můžeme tyto hodnoty dále zpacovávat nebo úplně měnit či transformovat. 

==== IF, ELSE ====
Struktura IF, ELSE umožňuje vyhodnotit logický výraz a dle jeho výsledku vykonat dotaz.
== Syntax: ==
<code sql>
IF Boolean_expression { sql_statement | statement_block } 
        [ ELSE { sql_statement | statement_block } ] 
</code>
== Příklad: ==
<code sql>
DECLARE @Condition int
SET @Condition = 250

IF @Condition > 100
  BEGIN
    SELECT TOP 100 * FROM table
  END
ELSE
  BEGIN
    SELECT TOP @Condition * FROM table
  END
</code>

==== CASE ====
Funkce CASE umožňuje vracet hodnotu na základě vyhodnocení jednoho, nebo více výrazů.
== Syntax: ==
<code sql>
CASE input_expression 
     WHEN when_expression THEN result_expression [ ...n ] 
     [ ELSE else_result_expression ] 
END 

</code>
== Příklad ==
<code sql>
SELECT firstname, surname,
CASE gender
  WHEN 'male' THEN 'M'
  WHEN 'female' THEN 'F'
ELSE 'UNKNOWN'
age, birthdate FROM peoples
</code>

===== Řízení toku =====
  * T-SQL umožňuje zpracovávat bloky příkazů opakovaně, pohybovat se v nich, nebo je s nějakou podmínkou okamžitě opouštět. Pomocí cyklu WHILE můžeme vykonávat blok příkazů dokud není splněná námi definovaná podmínka, pomocí příkazu GOTO se můžeme dle uvážení pohybovat v bloku příkazů nebo bloky opouštet pomocí pžíkazu RETURN
==== RETURN ====
RETURN umožňuje okamžitě opustit aktuální dotaz, dávku, nebo proceduru a vrátit se do nadřazeného bloku s návratovou hodnotou.
== Syntax: ==
<code sql>
RETURN [ integer_expression ] 
</code>
== Příklad: ==
<code sql>
BEGIN
  SELECT firstname,surname FROM anytable
  IF (@@ROWCOUNT = 0) RETURN
  UPDATE ...
  ...
END
</code>
==== WHILE ====
Umožňuje v cyklu vykonávat příkaz nebo dávku příkazů.
== Syntax: ==
<code sql>
WHILE Boolean_expression 
     { sql_statement | statement_block | BREAK | CONTINUE } 
</code>
== Příklad: ==
<code sql>
DECLARE @nomrows int
WHILE(SELECT @numrows=count(*) FROM anytable WHERE columnA>10)
BEGIN
  IF @numrows=1 BREAK
  UPDATE anytable SET columnA=(columnA+1)
END
</code>
==== GOTO ====
Umožňuje provádět skoky v dávkách SQL.
== Syntax: ==
<code sql>
GOTO label
</code>
== Příklad: ==
<code sql>
...
UPDATE anytable SET columnA=1
IF @@ROWCOUNT = 0 GOTO endblock
UPDATE anytable SET columnB=2
...
...
endblock:
RETURN
</code>

===== Kurzory =====
Kurzory umožňují sekvenčně zpracovávat řádky dotazu. Někdy je zapotřebí zpracovávat řádky jeden po druhém a ne celou sadu a právě k tomu kurzory slouží. Nicméně pokud to není nezbytně zapotřebí je lépe použít jiné konstrukce. Kurzory mohou totiž způsobit při svém běhu některé problémy, jedním z nich je velká paměťová náročnost, kurzory mohou způsobit i vyčerpání veškeré dostupné paměti, další problém nám může způsobit zamykání prostředků, ale asi největším jejich nedostatkem je rychlost, MSSQL server je založen na práci se sadami řádků a ne se sekvenčním zpracováním, proto je práce se sadami vždy rychlejší. Někdy však vaše aplikace použití kurzorů vyžaduje a vy jejich funkcionalitu velmi oceníte. \\
Kurzor se definuje SQL dotazem, který vrací sadu řádků. Řádky se pak z něj dají brát postupně po jednom, nebo po blocích a to v obou směrech podle definice kurzoru. Když skončíte práci s kurzorem je nutné ho explicitně uzavřít a dealokovat, aby jste uvolnili veškeré prostředky, které kurzor alokoval. Pokud tak neučiníte kurzor zůstane v paměti a bude jí blokovat. Problém může také nastat pokud by jste alokovali dva stejně pojmenované kurzory (i když nejsou ve stejném bloku!), je lépe tomu předcházet a v každé proceduře pojmenovávat kurzor dle jeho funkce jiným názvem.

=== Životní cyklus kurzoru ===
  - deklarace kurzoru
  - otevření kurzoru
  - práce s řádky
  - uzavření kurzoru
  - dealokace kurzoru

=== Deklarace kurzoru ===
  * už při deklaraci kurzoru definujeme jak s ním budeme pracovat a také se kterými daty, při jeho deklaraci určujeme SQL dotazem se kterými daty budem pracovat a dodatečným deklaracemi můžeme určit jestli budem kurzor otevírat pouze pro čtení, jestli se budem pohybovat pouze dopředu nebo i zpět, nebo třeba viditelnost kurzoru

<code sql>
DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR 
     FOR select_statement 
     [ FOR { READ ONLY | UPDATE [ OF column_name [ ,...n ] ] } ]
[;]
Transact-SQL Extended Syntax
DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ] 
     [ FORWARD_ONLY | SCROLL ] 
     [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ] 
     [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ] 
     [ TYPE_WARNING ] 
     FOR select_statement 
     [ FOR UPDATE [ OF column_name [ ,...n ] ] ]
[;]
</code>

== Příklad: ==
<code sql>
DECLARE cursorABC CURSOR
FOR SELECT columnA,columnB,columnC FROM anytable
</code>

=== Otevření kurzoru ===
  * příkazem OPEN kurzor naplníme

== Syntax: ==
<code sql>
OPEN { { [ GLOBAL ] cursor_name } | cursor_variable_name }
</code>

== Příklad: ==
<code sql>
OPEN cursorABC 
</code>

=== Práce s řádky ===
  * pracovat s řádky můžeme pomocí příkazu FETCH kterým načítáme do proměnných hodnoty z jednotlivých řádků, výsledek volání fetch je uložen v proměnné @@FETCH_STATUS (0 úspěch, -1 neúspěch, -2 chybějící řádek)

== Syntax: ==
<code sql>
FETCH 
          [ [ NEXT | PRIOR | FIRST | LAST 
                    | ABSOLUTE { n | @nvar } 
                    | RELATIVE { n | @nvar } 
               ] 
               FROM 
          ] 
{ { [ GLOBAL ] cursor_name } | @cursor_variable_name } 
[ INTO @variable_name [ ,...n ] ] 
</code>

== Příklad: ==
<code sql>
FETCH NEXT FROM cursorABC INTO @A,@B,@C
</code>

=== Zavření a dealokace kurzoru ===
  * příkazem CLOSE uvolníme paměť, kterou alokoval kurzor pro načtení rádků
  * příkazem DEALOCATE uvolníme veškeré prostředky, které kurzor alokoval 
== Syntax: ==
<code sql>
CLOSE { { [ GLOBAL ] cursor_name } | cursor_variable_name }

DEALLOCATE { { [ GLOBAL ] cursor_name } | @cursor_variable_name }
</code>

== Příklad: ==
<code sql>
CLOSE cursorABC 
DEALLOCATE cursorABC 
</code>

=== Komplexní příklad: ===
<code sql>
-- deklarace proměnných
DECLARE @A int 
DECLARE @B varchar(10)
DECLARE @C decimal(10,2)
-- deklarace kurzoru
DECLARE cursorABC CURSOR
FORWARD_ONLY
FOR SELECT columnA,columnB,columnC FROM anytable
-- otevření kurzoru
OPEN cursorABC 
-- načte řádek do kurzoru
FETCH NEXT FROM cursorABC INTO @A,@B,@C
-- cyklus pro zpracování všech řádek
WHILE @@FETCH_STATUS = 0
BEGIN
-- zde můžeme zpracovávat řádky
  print @A
  print @B
  print @C
-- načte další řádek
FETCH NEXT FROM cursorABC INTO @A,@B,@C
END
-- uzavření kurzoru
CLOSE cursorABC 
-- dealokace kurzoru
DEALLOCATE cursorABC 
</code>

===== Uložené procedury =====

Uložené procedura je seskupení příkazů T-SQL do jediného objektu uloženého na SQL serveru. Když se procedura vykonává poprvé po nastartovaní instance SQL serveru, uloží se do cache plán optimálního přístupu k datům, server pak tento plán využívá při každém dalším spuštění, čímž může urychlit vykonání dotazu. To je jedna a ne jediná z výhod uložených procedur. Další výhodou je znovupoužitelnost kódu, namísto aby jste všude kde je potřeba definovali jednoúčelový blok příkazů je lepší využít uloženou proceduru. Pokud by se tento kód měl vykonávat v nějaké z vašich aplikací jejich výhody se znásobí. Pokud by se třeba změnilo uložení dat v tabulkách, musíte vaši aplikaci popřípadě více aplikací na všech místech přepsat a v horším případě i překompilovat, ale pokud používáte uloženou proceduru, stačí ji změnit a pro aplikace se v podstatě nic nestalo. Použití procedur je také z pohledu db administrátora bezpečnější než přímý přístup do tabulek.

==== Vytvoření uložené procedury ====

== Syntax: ==
<code sql>
CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
    [ { @parameter [ type_schema_name. ] data_type } 
        [ VARYING ] [ = default ] [ OUT | OUTPUT ] [READONLY]
    ] [ ,...n ] 
[ WITH <procedure_option> [ ,...n ] ]
[ FOR REPLICATION ] 
AS { <sql_statement> [;][ ...n ] | <method_specifier> }
[;]
<procedure_option> ::= 
    [ ENCRYPTION ]
    [ RECOMPILE ]
    [ EXECUTE AS Clause ]

<sql_statement> ::= 
{ [ BEGIN ] statements [ END ] }

<method_specifier> ::=
EXTERNAL NAME assembly_name.class_name.method_name
</code>

== Příklad: ==
<code sql>
CREATE procedure [dbo].[MyProcedure]
@pid int,@other int,@sometext varchar(100)
AS


IF ISNULL(@pid,0)=0
  RETURN

EXEC ExecuteAnotherProcedure @pid

IF ISNULL(@other ,0)<>0
 BEGIN
	DECLARE @a int
	SELECT @a=a FROM anytable WHERE a=@pid and b=@other

	UPDATE sometable SET a=@pid WHERE b=@other
 END

</code>

==== Úprava a odstranění uložené procedury ====

== Syntax: ==
<code sql>
-- úprava procedury
ALTER { PROC | PROCEDURE } [schema_name.] procedure_name [ ; number ] 
...
same as CREATE
-- odstranění procedury
DROP { PROC | PROCEDURE } { [ schema_name. ] procedure } [ ,...n ]
</code>

==== Zašifrování uložené procedury ====
  * někdy je potřeba proceduru zabezpečit, aby nikdo nemohl neoprávněně získat její definici , zejména pokud řešíme část aplikační logiky uloženými procedurami.
  * po zašifrování uložené procedury už si nelze prohlídnout její obsah!
== Syntax: ==
<code sql>
CREATE PROCEDURE [dbo].[MyProcedure]
WITH ENCRYPTION
AS
...
</code>


===== Uživatelsky definované funkce =====
  * uživatelsky definované funkce mají stejně jako uložené procedury mnoho výhod - ukládání do cache, znovupoužití kódu atd... narozdíl od procedur však můžeme funkce použít i tam kde procedury ne (například v klauzuli SELECT)
  * Typy uživatelsky definovaných funkcí
    * Skalární uživatelsky definované funkce
    * Přímé tabulkové uživatelsky definované funkce
    * Vícepříkazové uživatelsky definované funkce

==== Skalární uživatelsky definované funkce ====
  * mohou přebírat nula nebo více argumentů
  * vrací jedinou hodnotu

== Syntax: ==
<code sql>
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ][ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)
RETURNS return_data_type
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN scalar_expression
    END
[ ; ]
</code>

== Příklad: ==
<code sql>
CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
            AS
    BEGIN
    DECLARE @rtDayofWeek VARCHAR(10)
    SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate)
                    WHEN 1 THEN 'Monday'
                    WHEN 2 THEN 'Tuesday'
                    WHEN 3 THEN 'Wednesday'
                    WHEN 4 THEN 'Thursday'
                    WHEN 5 THEN 'Friday'
                    WHEN 6 THEN 'Saturday'
                    WHEN 7 THEN 'Sunday'
        END
    RETURN (@rtDayofWeek)
    END
GO

-- použití funkce
SELECT udf_DayOfWeek(getdate())
</code>

==== Přímé tabulkové uživatelsky definované funkce ====
  * mohou přebírat nula nebo více argumentů
  * vrací data na základě jediného příkazu SELECT
  * odkazuje se na ně z klauzule FROM

== Syntax: ==
<code sql>
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [ READONLY ] } 
    [ ,...n ]
  ]
)

RETURNS TABLE
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    RETURN [ ( ] select_stmt [ ) ]
[ ; ]

</code>
== Příklad: ==
<code sql>
CREATE FUNCTION dbo.GetByCity(@city varchar(100))
RETURNS TABLE
AS
RETURN
(
SELECT * from peoples where city=@city
(
GO

-- použití funkce
SELECT * FROM GetByCity('Prague')
</code>

==== Vícepříkazové uživatelsky definované funkce ====
  * narozdíl od přímých tabulkových funkcí, které jsou omezeny na jediný příkaz select, mohou vícepříkazové funkce vrátit výsledek definovaný pomocí více príkazů T-SQL
== Syntax: ==
<code sql>
CREATE FUNCTION [ schema_name. ] function_name 
( [ { @parameter_name [ AS ] [ type_schema_name. ] parameter_data_type 
    [ = default ] [READONLY] } 
    [ ,...n ]
  ]
)
RETURNS @return_variable TABLE <table_type_definition>
    [ WITH <function_option> [ ,...n ] ]
    [ AS ]
    BEGIN 
                function_body 
        RETURN
    END
[ ; ]

</code>

== Příklad: ==
<code sql>

</code>

===== Odchytávání vyjímek =====
  * pokud chceme v našich SQL příkazech nebo procedurách odchytávat vyjímka, dává nám k tomu T-SQL nástroj v podobě konstrukce TRY...CATCH. Tato konstrukce je schopná zachytit vyjímky, které mohou nastat při vykonávání našeho kódu a vykonat nějakou reakci na vzniklou chybu. Například vygenerovat zprávu o chybě nebo anulovat transakci.
  * Nastaneli v bloku TRY nějaká vyjímka, skončí SQL server v místě kde nastala a vykoná příkazy v bloku catch. 
  * Výhoda této konstrukce je, že se do sebe může libovolně vnořovat
  * Konstrukce TRY nezachytí syntaktické chyby, nebo chybné názvy objektů

=== Funkce poskytující informace o chybách ===
  * ERROR_LINE - číslo řádky kde došlo k chybě
  * ERROR_MESSAGE - chybová zpráva
  * ERROR_NUMBER - číslo chyby
  * ERROR_PROCEDURE - název procedury kde došlo k chybě
  * ERROR_SEVERITY - závažnost chyby
  * ERROR_STATE - stav chyby

==== Konstrukce TRY...CATCH ====

== Syntax: ==
<code sql>
BEGIN TRY
     { sql_statement | statement_block }
END TRY
BEGIN CATCH
          [ { sql_statement | statement_block } ]
END CATCH
[ ; ]
</code>

== Příklad: ==
<code sql>
BEGIN TRY
     INSERT anyTable (columnA,columnB,columnC) VALUES (1,2,3)
END TRY
BEGIN CATCH
          print 'Chyba při vykonávání příkazu!'
END CATCH
</code>

==== Vytváření vlastních vyjímek ====
  * někdy potřebujeme na chyby reagovat vlastními vyjímkami, i na to má T-SQL nástroj, vlastní vyjímku můžeme přidat pomocí systémové procedury sp_addmessage a vyjímku vyvolávat příkazem RAISERROR
== Syntax: ==
<code sql>
sp_addmessage [ @msgnum = ] msg_id , [ @severity = ] severity , [ @msgtext = ] 'msg' 
     [ , [ @lang = ] 'language' ] 
     [ , [ @with_log = ] { 'TRUE' | 'FALSE' } ] 
     [ , [ @replace = ] 'replace' ] 

-- úprava vlastní vyjímky
sp_altermessage [ @message_id = ] message_number   , [ @parameter = ] 'write_to_log'
      , [ @parameter_value = ] 'value' 

-- odstraněni vyjímky
sp_dropmessage [ @msgnum = ] message_number
        [ , [ @lang = ] 'language' ]
</code>
== Příklad: ==
<code sql>
EXEC sp_addmessage 123456, 20, N'Došlo k mojí vyjímce!'
</code>

==== Vyvolávání vlastních vyjímek ====
  * vyvolat vlastní vyjímku můžeme pomocí RAISERROR

== Syntax: ==
<code sql>
RAISERROR ( { msg_id | msg_str | @local_variable }
    { ,severity ,state }
    [ ,argument [ ,...n ] ] )
    [ WITH option [ ,...n ] ]
</code>

== Příklad: ==
<code sql>
RAISERROR (123456, 20, 1, N'any.anytable' )
</code>

===== Zdroje =====

  * Velká kniha T-SQL & SQL Server 2005, Joseph Sack, Zonerpress 2007 
  * http://msdn.microsoft.com/en-us/library/ 
  * http://google.com