Domyślne wartości parametrów procedury:
create PROCEDURE Test
@vpesel varchar(30) = '12345678905'
AS
Select r.imie, r.nazwisko From osoba o, osoba r Where r.ojciec = o.ojciec and r.matka = o.matka and o.pesel = @vpesel and r.pesel <> o.pesel
go
exec test
Przechwytywanie i obsuga błedów:
declare @a numeric(4,2), @b numeric(4,2)
Set @a = 1
Set @b = 0
Begin Try
print @a/@b
End Try
Begin Catch
Select error_message() Komunikat, error_number() Numer, error_severity() Waznosc, error_state() Przyczyna
Print 'Nie dziel przez 0!!!'
End Catch
· A TRY block must be immediately followed by a CATCH block.
· TRY…CATCH constructs can be nested. This means that TRY…CATCH constructs can be placed inside other TRY and CATCH blocks. When an error occurs within a nested TRY block, program control is transferred to the CATCH block that is associated with the nested TRY block.
· To handle an error that occurs within a given CATCH block, write a TRY…...CATCH block within the specified CATCH block.
· Errors that have a severity of 20 or higher that cause the Database Engine to close the connection will not be handled by the TRY…CATCH block. However, TRY…CATCH will handle errors with a severity of 20 or higher as long as the connection is not closed.
· Errors that have a severity of 10 or lower are considered warnings or informational messages, and are not handled by TRY…CATCH blocks.
· Attentions will terminate a batch even if the batch is within the scope of a TRY…CATCH construct. This includes an attention sent by the Microsoft Distributed Transaction Coordinator (MS DTC) when a distributed transaction fails. MS DTC manages distributed transactions.
Zasięg zmiennych:
Lokalny
Przeciążanie nazw:
Brak, nazwa procedury musi być unikatowa w schemacie
Case:
Declare @a int
set @a = 4
Select Case @a
when 1 then 'a = 1'
when 2 then 'a = 2'
when 3 then 'a = 3'
else 'a <> 0'
End
Jest to typowy Case z SQL-a zwracający Recordset, nie ma instrukcji Case, która byłaby częścią samego Transact SQL-a.
http://msdn2.microsoft.com/en-us/library/ms179296.aspx
yoquero