NOTLARIMDAN...

18 Kasım 2011 Cuma

Dinamik SQL cümleleri çalıştırmak

PROCEDURE kısaltması PROC
TRANSACTION kısaltması TRANS
EXECUTE kısaltması EXEC

Demek ki EXECUTE = EXEC
eğer parametresiz bir SQL cümlesi ise EXEC '....' ya da EXEC @sqlCumlesi
Eğer sql cümleniz: >

'SELECT TOP(@iTopIN) @soyadiOut=soyadi FROM @tabloAdiIN WHERE adi=@kullaniciAdiIN'

ise
@iTopIN, @tabloAdiIN ve @kullaniciAdiIN diye 3 parametre girdi parametresi
@soyadiOUT ise çıktı (OUT|OUTPUT) parametresi.
Böyle bir dinamik SQL ifadesini aşağıdaki örnekteki gibi hazırlayabilirsiniz.

Eğer SQL ifadenizde parametre ifadeleriniz varsa EXEC(@sqlIfadeniz) şeklinde parentezli olarak çalıştırmalısınız.

DECLARE @tabloAdi sysname;
DECLARE @SQL nvarchar(500);
DECLARE @ParmDef nvarchar(500);
DECLARE @count int;

SET @tabloAdi = '[dbo].[Birimler]';
SET @SQL = N'SELECT @cnt=COUNT(1) FROM '+@tabloAdi+';' 
SET @ParmDef = N' @tbl sysname, @cnt int OUTPUT';

EXECUTE sp_executesql @SQL, @ParmDef, @tbl = @tabloAdi, @cnt=@count OUTPUT;
SELECT @count;

Aşağıdaki kaynak kodu LOG kayıtlarımızı taşımak için yazdığımız bir SQL JOB.

DECLARE @kaynak VARCHAR(50)
DECLARE @hedef VARCHAR(50)
DECLARE @aktarilan INT
DECLARE @silinen INT
DECLARE @tblOzet TABLE
(
    id int IDENTITY(1,1),
    kaynak VARCHAR(50),
    hedef VARCHAR(50),
    aktarilan int,
    silinen int
);


DECLARE cr CURSOR FOR SELECT kaynakTabloAdi, hedefTabloAdi FROM medula_V3_test_test_log.dbo.TasinacakTablolar
OPEN cr

 FETCH NEXT FROM cr INTO @kaynak, @hedef

 WHILE @@FETCH_STATUS = 0
 BEGIN
  print @kaynak+' - '+@hedef
  -- 1. Kaynakta şu an kaç aktarılacak satır var (COUNT)
  -- 2. Hedefe TOP(COUNT) kayıdı, kaynaktan SELECT ile INSERT et
  -- 3. Kaynaktaki TOP(COUNT) kayıdı sil
  -- 4. Özet durum mesajı hazırla
    /*
    
     
      KAYNAK HEDEF AKTARILAN SİLİNEN
     
     
      @kaynak @hedef @aktarilan @silinen
     
     
    */
  -- 5. Özet durumu ileti olarak gönder

  DECLARE @sql VARCHAR(500)
  SET @sql ='INSERT INTO '+@hedef +' SELECT * FROM '+@kaynak
  EXEC(@sql)
  SET @aktarilan = @@ROWCOUNT

  SET @sql = 'DELETE TOP('+CAST(@aktarilan AS NVARCHAR(8))+') FROM '+@kaynak
  EXEC(@sql)
  SET @silinen = @@ROWCOUNT

  INSERT INTO @tblOzet VALUES(@kaynak, @hedef, @aktarilan, @silinen);

     FETCH NEXT FROM cr INTO @kaynak,@hedef
 END

CLOSE cr
DEALLOCATE cr

SELECT * FROM @tblOzet

KAYNAK	HEDEF	AKTARILAN	SİLİNEN
@kaynak	@hedef	@aktarilan	@silinen

Sonuç:

Taşınacak tabloların tutulduğu tablo:

Son Hali

DECLARE @kaynakVeritabani VARCHAR(50)
SET @kaynakVeritabani = 'medula_V3_test';
DECLARE @hedefVeritabani VARCHAR(50)
SET @hedefVeritabani = 'medula_V3_test_test_log';

-- @mailBody : Gönderilecek iletinin gövdesini oluşturacak
DECLARE @mailBody NVARCHAR(MAX)
-- @tr : Gönderilecek iletinin tablosundaki satırı oluşturmak için
DECLARE @tr NVARCHAR(1000)

DECLARE @kaynak VARCHAR(50)
DECLARE @hedef VARCHAR(50)
DECLARE @aktarilan INT
DECLARE @silinen INT
DECLARE @tblOzet TABLE (id int IDENTITY(1,1), kaynak VARCHAR(50), hedef VARCHAR(50), aktarilan int, silinen int);

DECLARE cr CURSOR FOR SELECT kaynakTabloAdi, hedefTabloAdi FROM medula_V3_test_test_log.dbo.TasinacakTablolar
OPEN cr

 FETCH NEXT FROM cr INTO @kaynak, @hedef

 WHILE @@FETCH_STATUS = 0
 BEGIN
  -- 1. Hedefteki kayıtları, kaynaktan SELECT ile hedefe INSERT et
  -- 2. Kaynaktaki TOP(@@ROWCOUNT) kayıdı sil ve işlem sonuçlarını özet tabloya INSERT et
  -- 3. Kaynaktaki Veritabanı sıkıştırılaracak
  -- 4. Özet durum mesajı hazırla
    /*
    
     
      KAYNAK HEDEF AKTARILAN SİLİNEN
     
     
      @kaynak @hedef @aktarilan @silinen
     
     
    */
  -- 5. Özet durumu ileti olarak gönder

  -- 1.
  DECLARE @sql VARCHAR(500)
  SET @sql ='INSERT INTO '+ @hedef + ' SELECT * FROM ' + @kaynak
  EXEC(@sql)
  SET @aktarilan = @@ROWCOUNT

  -- 2.
  SET @sql = 'DELETE TOP(' + CAST(@aktarilan AS NVARCHAR(8)) + ') FROM ' + @kaynak
  EXEC(@sql)
  SET @silinen = @@ROWCOUNT

  INSERT INTO @tblOzet VALUES(@kaynak, @hedef, @aktarilan, @silinen);

     FETCH NEXT FROM cr INTO @kaynak,@hedef
 END

CLOSE cr
DEALLOCATE cr


-- 3. iş DB Sıkıştırma
DBCC SHRINKDATABASE ( @kaynakVeritabani )

-- 4.
SET @mailBody ='

';
DECLARE crHtml CURSOR FOR SELECT kaynak,hedef,aktarilan,silinen FROM @tblOzet
OPEN crHtml
FETCH NEXT FROM crHtml INTO @kaynak, @hedef, @aktarilan, @silinen

 WHILE @@FETCH_STATUS = 0
 BEGIN
  SET @tr = ''
  SET @mailBody = @mailBody + @tr
     FETCH NEXT FROM crHtml INTO @kaynak, @hedef, @aktarilan, @silinen
 END

CLOSE crHtml
DEALLOCATE crHtml

SET @mailBody = @mailBody + '
 
  KAYNAK HEDEF AKTARILAN SİLİNEN
 
'+@kaynak+' '+@hedef+' '+CAST(@aktarilan AS NVARCHAR(10))+' '+ CAST(@silinen AS NVARCHAR(10))+'';



-- 5. Adım İleti gönderme
DECLARE @body VARCHAR(1000)
SET @body = '

LOG Kayıtlarının Taşınması ÖZETİ
Çalışma Tarihi: ' + GETDATE() + '
Kaynak Veritabanı: ' + @kaynakVeritabani + '
Aktarılan Veritabanı: ' + @hedefVeritabani + '
' + @mailBody + '';

DECLARE @args VARCHAR(1000)
SET @args = 'Q:\caSendEmailAlert.exe /sbj:["Log Taşıma"] /too:cem.topkaya@fmc-ag.com,duygu.akmaz@fmc-ag.com /bdy:["' + @body + '"]'
EXEC xp_cmdshell @args

KAYNAK	HEDEF	AKTARILAN	SİLİNEN
@kaynak	@hedef	@aktarilan	@silinen

KAYNAK	HEDEF	AKTARILAN	SİLİNEN
'+@kaynak+'	'+@hedef+'	'+CAST(@aktarilan AS NVARCHAR(10))+'	'+ CAST(@silinen AS NVARCHAR(10))+'

LOG Kayıtlarının Taşınması ÖZETİ
Çalışma Tarihi:	' + GETDATE() + '
Kaynak Veritabanı:	' + @kaynakVeritabani + '
Aktarılan Veritabanı:	' + @hedefVeritabani + '
' + @mailBody + '

İleti Gönderen Uygulamaya göre düzenlenmiş

LOG Kayıtlarının Taşınması ÖZETİÇalışma Tarihi: Nov 19 2011 12:
Kaynak Veritabanı: medula_V3
Aktarılan Veritabanı: medula_V3_Log
KAYNAK HEDEF AKTARILAN SİLİNEN
dbo.LogBirimler dbo.LogBirimler 0 0

SON HALİ(çift tırnaklı falan)

DECLARE @kaynakVeritabani VARCHAR(50)
SET @kaynakVeritabani = 'medula_V3';
DECLARE @hedefVeritabani VARCHAR(50)
SET @hedefVeritabani = 'medula_V3_Log';

-- @html : Gönderilecek iletinin tüm HTML yapısı
DECLARE @html VARCHAR(4000)
-- @css : Gönderilecek iletinin style tanımları
DECLARE @css VARCHAR(200)
SET @css ='<link rel=''stylesheet'' href=''http://medula.fresenius.com.tr/OrtakDosyalar/FMedulaLogAktarimIleti.css'' style type=''text/css'' />';
-- @head : Gönderilecek iletinin HEAD kısmı
DECLARE @head VARCHAR(300)
SET @head = '<HEAD>' + @css + '</HEAD>'
-- @body : Gönderilecek iletinin BODY kısmı
DECLARE @body VARCHAR(3500)
-- @aktarimOzet : Aktarımın hangi DB den hangi DB ye ne zaman yapıldı özet başlığı
DECLARE @aktarimOzet VARCHAR(3500)
-- @aktarimSonuc : Aktarım bilgisinin(aktarımı yapılmış tablo adlarının ve aktarılan kayıt sayılarının) <TABLE> içinde gösterimi
DECLARE @aktarimSonuc VARCHAR(3500)
-- @tr : Gönderilecek iletinin tablosundaki satırı oluşturmak için
DECLARE @tr VARCHAR(1000)

DECLARE @iletiKime VARCHAR(200)
SET @iletiKime = 'cem.topkaya@fmc-ag.com'

-- KAYITLAR içinde dönmek için kullanılacak ve aktarılmışların özet bilgilerini tutumak için değişkenler
DECLARE @kaynak VARCHAR(50)
DECLARE @hedef VARCHAR(50)
DECLARE @aktarilan INT
DECLARE @silinen INT
DECLARE @tblOzet TABLE (id int IDENTITY(1,1), kaynak VARCHAR(50), hedef VARCHAR(50), aktarilan int, silinen int);


DECLARE cr CURSOR FOR SELECT kaynakTabloAdi, hedefTabloAdi FROM medula_V3_Log.dbo.TasinacakTablolar
OPEN cr

 FETCH NEXT FROM cr INTO @kaynak, @hedef

 WHILE @@FETCH_STATUS = 0
 BEGIN
  -- 1. Hedefteki kayıtları, kaynaktan SELECT ile hedefe INSERT et
  -- 2. Kaynaktaki TOP(@@ROWCOUNT) kayıdı sil ve işlem sonuçlarını özet tabloya INSERT et
  -- 3. Kaynaktaki Veritabanı sıkıştırılaracak
  -- 4. Özet durum mesajı hazırla
    /*
    <TABLE>
     <THEAD><TR>
      <TH>KAYNAK</TH><TH>HEDEF</TH><TH>AKTARILAN</TH><TH>SİLİNEN</TH>
     </TR></THEAD>
     <TBODY>
      <TR><TD>@kaynak</TD><TD>@hedef</TD><TD>@aktarilan</TD><TD>@silinen</TD></TR>
     </TBODY>
    </TABLE> 
    */
  -- 5. Özet durumu ileti olarak gönder

  -- 1.
  DECLARE @sql VARCHAR(500)
  SET @sql ='INSERT INTO '+ @hedefVeritabani +'.'+ @hedef + ' SELECT * FROM ' + @kaynakVeritabani +'.'+ @kaynak
  EXEC(@sql)
  SET @aktarilan = @@ROWCOUNT;

  -- 2.
  SET @sql = 'DELETE TOP(' + CAST(@aktarilan AS NVARCHAR(8)) + ') FROM ' + @kaynakVeritabani +'.'+ @kaynak
  EXEC(@sql)
  SET @silinen = @@ROWCOUNT;

  INSERT INTO @tblOzet VALUES(@kaynak, @hedef, @aktarilan, @silinen);

     FETCH NEXT FROM cr INTO @kaynak,@hedef
 END

CLOSE cr
DEALLOCATE cr


-- 3. iş DB Sıkıştırma
DBCC SHRINKDATABASE ( @kaynakVeritabani )

-- 4.
SET @aktarimSonuc = '<TABLE border=''1''><THEAD><TR><TH class=''metin''>KAYNAK</TH><TH class=''metin''>HEDEF</TH><TH>AKTARILAN</TH><TH>SİLİNEN</TH></TR></THEAD>';

DECLARE crHtml CURSOR FOR SELECT kaynak,hedef,aktarilan,silinen FROM @tblOzet
OPEN crHtml
FETCH NEXT FROM crHtml INTO @kaynak, @hedef, @aktarilan, @silinen

 WHILE @@FETCH_STATUS = 0
 BEGIN
  IF @aktarilan > 0
  BEGIN
   SET @tr = '<TR><TD>'+@kaynak+'</TD><TD>'+ @hedef +'</TD><TD class=''rakam''>'+CAST(@aktarilan AS VARCHAR(10))+'</TD><TD  class=''rakam''>'+ CAST(@silinen AS VARCHAR(10))+'</TD></TR>'
   SET @aktarimSonuc = @aktarimSonuc + @tr
  END
     FETCH NEXT FROM crHtml INTO @kaynak, @hedef, @aktarilan, @silinen
 END

CLOSE crHtml
DEALLOCATE crHtml

SET @aktarimSonuc = @aktarimSonuc + '</TBODY></TABLE>';



-- 5. Adım İleti gönderme
SET @aktarimOzet = '<TABLE><CAPTION>LOG TAŞIMA ÖZETİ</CAPTION><TR><TH class=''metin''>Çalışma Zamanı : </TH><TD>' + CAST(GETDATE() AS VARCHAR(25)) + '</TD></TR><TR><TH class=''metin''>Kaynak VT: </TH><TD>' + @kaynakVeritabani + '</TD></TR><TR><TH class=''metin''>Hedef VT: </TH><TD>' + @hedefVeritabani + '</TD></TR><TR><TD COLSPAN=''2''>' + @aktarimSonuc + '</TD></TR></TABLE>';
SET @body = '<BODY>' + @aktarimOzet + '</BODY>';
SET @html = '<HTML>' + @head + @body + '</HTML>';
--print @html

DECLARE @args VARCHAR(4000)
SET @args = 'Q:\SendMail\caSendEmailAlert.exe /sbj:["Log Taşıma"] /html /to:'+ @iletiKime +' /body:["' + @html + '"]'
--print @args
EXEC xp_cmdshell @args

Aklımda Kalası Kelimeler

18 Kasım 2011 Cuma

Dinamik SQL cümleleri çalıştırmak

Son Hali

İleti Gönderen Uygulamaya göre düzenlenmiş

SON HALİ(çift tırnaklı falan)