Lietotāja definētas funkcijas (kas atgriež tabulu)

Iepriekš rakstīju par Iekļautām (inline) lietotāja definētām funkcijām. Arī tās atgriež tabulu, tomēr būtiska nianse ir tā, ka tādas funkcijas var strādāt tikai ar vienu vaicājumu. Pielāgojot datus pirms to atgriešanas (vairākas t-sql komandas) ir jāizmanto sarežģītāka sintakse- par to turpinājumā.

Šādā gadījumā, definējot funkciju tiek definēta arī tabula, kas tiks atgriezta (atgriežot sarežģītāku struktūru visai daudz darba to aprakstīt). Un tabula tiek definēta kā lietotāja definēts tips, ar kuru funkcijas iekšienē var darīt visu, ko vien ar šo tipu var darīt- ievietot, dzēst, mainīt ierakstus.

Vēl viena lieta, kas jāievēro- funkcijas beigās ir "return" atslēgvārds, BEZ parametriem. pēc "return" izpildes funkcija savu darbu beidz un tiek atgriezta tabula (tāda, kāda līdz tam brīdim ir saveidota).

Funkcija varētu izskatīties, piemēram, šāda (pilns skripts ar piemēra tabulas izveidi, datiem un satīrīšanas skriptu, raksta beigās):

create Function fn_getDataFromTemp
(
    @Nodala int
)
Returns @test Table
(
    Nodala int,
    Darbinieki nvarchar(max)
)
As
Begin
    DECLARE @s NVARCHAR(max)
    DECLARE @n NVARCHAR(50)
    DECLARE c CURSOR   
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
        SELECT Vards FROM dbo.Temp Where Nodala = @Nodala
      
    OPEN c;      
    FETCH NEXT FROM c INTO @n;      
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @s = COALESCE(@s + ';' + @n, @n);          
        FETCH NEXT FROM c INTO @n;      
    END
    CLOSE c;
    DEALLOCATE c;
   
    Insert into @test(Nodala, Darbinieki)
    Values (@Nodala, @s)
    return;
End

Funkcijas iekšienē tiek ar kursora palīdzību salikti vārdi darbiniekiem, kas strādā attiecīgajā nodaļā (pats kursors paņemts no raksta Teksta savirknēšana).

Vairāk par UDF, kas atgriež tabulu var lasīt msdn.

Pilns skripts:

Create Table dbo.Temp
(
    TempID int primary key identity,
    Nodala int,
    Vards nvarchar(50),
    Uzvards nvarchar(50)
)
Go
Insert Into dbo.Temp(Nodala, Vards, Uzvards) Values (1, 'Janis', 'Kocins');
Insert Into dbo.Temp(Nodala, Vards, Uzvards) Values (2, 'Ilze', 'Liepiņa');
Insert Into dbo.Temp(Nodala, Vards, Uzvards) Values (2, 'Vija', 'Rītiņa');
Go
create Function fn_getDataFromTemp
(
    @Nodala int
)
Returns @test Table
(
    Nodala int,
    Darbinieki nvarchar(max)
)
As
Begin
    DECLARE @s NVARCHAR(max)
    DECLARE @n NVARCHAR(50)
    DECLARE c CURSOR   
    LOCAL STATIC FORWARD_ONLY READ_ONLY
    FOR
        SELECT Vards FROM dbo.Temp Where Nodala = @Nodala
      
    OPEN c;      
    FETCH NEXT FROM c INTO @n;      
    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @s = COALESCE(@s + ';' + @n, @n);          
        FETCH NEXT FROM c INTO @n;      
    END
    CLOSE c;
    DEALLOCATE c;
   
    Insert into @test(Nodala, Darbinieki)
    Values (@Nodala, @s)
    return;
End
Go
Select * From fn_getDataFromTemp(default)
Select * From dbo.fn_getDataFromTemp(1)
Select * From dbo.fn_getDataFromTemp(2)
Go
Drop Table dbo.Temp
Drop Function dbo.fn_getDataFromTemp