MsSQL Server Memtable ve Cursor kullanımına örnek

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER  PROCEDURE [dbo].[GetPdksData]
(@TIP NVARCHAR(30))
AS
DECLARE @MEMTABLE TABLE
(
   IP NVARCHAR(30) Null,
   TERM_NO INT Null,
   PERSONEL_SICIL_NO NVARCHAR(30) Null,
   PERSONEL_KART_NO NVARCHAR(30) Null,
   KART_SICIL_NO NVARCHAR(30) Null
)

--Cur1
DECLARE @HAT_NO INT;
DECLARE @IP VARCHAR(30);

--Cur2
DECLARE @TERM_NO INT;
DECLARE @TERM_TIPI INT;
DECLARE @HES_TIPA INT;

--Cur3
DECLARE @GRUP_ID INT;


--Cur4
DECLARE @PERSONEL_SICIL_NO NVARCHAR(30)
DECLARE @PERSONEL_KART_NO NVARCHAR(30)
DECLARE @KART_SICIL_NO NVARCHAR(30)

BEGIN
   --SET NOCOUNT ON
   DECLARE Cur1 CURSOR
   FOR SELECT sira, ip FROM tyol WHERE tno = @TIP;
   OPEN Cur1

   FETCH NEXT FROM Cur1
   INTO @HAT_NO, @IP;
 
   WHILE @@FETCH_STATUS=0
   BEGIN
            --Start Cur2
       DECLARE Cur2 CURSOR
       FOR SELECT sira, TERM_TIPI, hart FROM terminal WHERE HAT_NO =  @HAT_NO
       OPEN Cur2

       FETCH NEXT FROM Cur2
       INTO @TERM_NO, @TERM_TIPI, @HES_TIPA
       WHILE @@FETCH_STATUS=0
       BEGIN


           --PRINT @RTERM_NO
           --Start Cur3
           DECLARE Cur3 CURSOR 
	      FOR SELECT GRUP_ID FROM gruplar WHERE (SUBSTRING(Terminal, 2*(@TERM_NO-1)+1,1) = 1 OR SUBSTRING(Terminal, (2*(@TERM_NO)),1) = 1)
           OPEN Cur3

           FETCH NEXT FROM Cur3
           INTO @GRUP_ID
           WHILE @@FETCH_STATUS=0
           BEGIN

               --Start Cur4
               DECLARE Cur4 CURSOR
               FOR SELECT Sicil_No,Kart_No,Chk_Sicil  FROM personel WHERE GRUP_ID = @GRUP_ID
               OPEN Cur4

               FETCH NEXT FROM Cur4
               INTO @PERSONEL_SICIL_NO,
                @PERSONEL_KART_NO,
                @KART_SICIL_NO
               --PRINT @PERSONEL_SICIL_NO
               WHILE @@FETCH_STATUS=0
               BEGIN
                   INSERT INTO @MEMTABLE VALUES(@IP, @TERM_NO,
@PERSONEL_SICIL_NO,@PERSONEL_KART_NO,@KART_SICIL_NO);
                   SET @PERSONEL_SICIL_NO = 0;
                   FETCH NEXT FROM Cur4
                     INTO @PERSONEL_SICIL_NO,
                       @PERSONEL_KART_NO,
                      @KART_SICIL_NO
              END
               CLOSE Cur4
               DEALLOCATE Cur4
               --End Cur4
               SET @GRUP_ID = 0;
               FETCH NEXT FROM Cur3
               INTO @GRUP_ID
           END                     CLOSE Cur3             DEALLOCATE Cur3                 --End Cur3
                    SET @TERM_NO        = 0;
           SET @TERM_TIPI    = 0;
           SET @HES_TIPA    = 0;


           FETCH NEXT FROM Cur2
           INTO @TERM_NO, @TERM_TIPI, @HES_TIPA

       END
       CLOSE Cur2
       DEALLOCATE Cur2
       --End Cur2
                --PRINT @IP
       SET @HAT_NO = 0;
       SET @IP = '';

       FETCH NEXT FROM Cur1
       INTO @HAT_NO, @IP;
   END
   CLOSE Cur1
   DEALLOCATE Cur1
 
   SELECT * FROM @MEMTABLE
 
END