Dinamik Sorgular (Stored Procedure Örnekleri)

Bu yazımızda SQL Serverda iki ayrı veritabanımızdaki aynı field ve tiplere sahip tablolar arasında işlem yapacağız. Bunun için dinamik sql cümleleri oluşturup bunların sorgularını işleyip sonuçlarına göre iki veritabanındaki tablolarda farklı kayıtları bulacağız. Bu farklı kayıtların identity insert on ve off komutlarını kullanarak aynı id ile kaynak veritabanından hedef veritabanındaki tabloya kaydını oluşturacağız.  

Hemen hemen hepimiz Veritabanı oluştururken bir reel veritabanı bir de test veritabanı kullanıyoruz. Zaman zaman reel veritabanında oluşturduğumuz kayıtların test veritabanındaki aynı tabloya aktarılması veya bu işlemin tam tersi durumlara ihtiyaç duyduğumuz oluyor. Bunun için bir kaç aşamadan oluşan procedure lar ile bu işlemi gerçekleştirmeye çalışacağım.

Sorgularımız baştan sona dinamik olacağı için kod tekrarına düşmemek amacıyla bazı sorgularımız için yardımcı stored procedure lar oluşturacağız. Konunun sonunda bu yardımcı kodlar için neden function oluşturmadık bunun yerine stored procedure lar yazdık diye soracak olursanız şimdiden söyleyeyim functionlar da exec sp_execute_sql kullanmamıza izin vermiyor.  

İlk procedure muz bir tablonun fieldlarını virgülle peşpeşe sıralayarak veren kodumuz olacak. 

Sys_GetTableColumns:

Create procedure [dbo].[sys_GetTableColumns]
	@DatabaseName		nvarchar(max),
	@TableName			nvarchar(max), 
	@Returned			nvarchar(max) output
as
begin
	Declare @SqlValue nvarchar(max)
	
	set @SqlValue = 'Select @Returned = Stuff(
		(
			Select	'', '' + ColumnList.name 
			From ' + @DatabaseName + '.sys.columns ColumnList 
			INNER JOIN ' + @DatabaseName + '.sys.tables TableList 
			on TableList.object_id = ColumnList.object_id Where TableList.name=''' + @TableName + ''' 
			For Xml path('''')),1,2,'''')' 
	exec sp_executesql @SqlValue,N'@Returned nvarchar(max) output', @Returned output
end

Burada dışarıdan parametre olarak veritabanı adı tablo adı ve sorgumuzun sonucunu dışarı dönebileceğimiz bir değişken oluşturuyoruz. Ardından Veritabanının sys columns tablosu ve sys tables tablolarını inner join ile ilişkisini tanımlayıp kolonların isimlerini aralarında virgül koyarak dışarı vermesini sağlayan sorğumuzu SqlValue değişkenine atıyoruz.

SqlValue daki sorgumuzu incelediğimizde iç sorgudaki kolon isimlerini For Xml Path ile aralarına virgül koyarak yanyana yazdırmasını sağlıyoruz.

Gelecek olan değerlerin en dıştaki @Returned değerine atılacağını belirtiyoruz. 

exec sp_executesql bölümünde ise dinamik sorgumuzun işlenmesini sağlıyoruz. Ardından verdiğimiz parametre ise @Returned değişkeninin sonucunun output ile dışarıda tanımladığımız değişkene atmasını sağlıyoruz. 

Bu stored procedure u aşağıdaki kod ile test edebilirsiniz. 

Stored Procedure test edecek olursak aşağıdaki gibi bir çıktımız olur.

Declare @Result nvarchar(max)
exec sys_GetTableColumns 'VERITABANIADI', 'XtraNameSpaces',@Result output
Select @Result
Result : 
XtraNameSpaceId, Type, Name, Caption, FullName, ParentXtraNameSpaceId

Yani select ile başlayan cümlemizin ardına bir tablonun kolonlarını yazmak istediğimizde bu procedure dan faydalanacağız.
Bir sonraki procedure miz ise tablonun primary key kolonunu bulmak. 

sys_GetTablePrimaryKey:

ALTER proc [dbo].[sys_GetTablePrimaryKey]
	@DatabaseName		nvarchar(max),
	@TableName			nvarchar(max),
	@Returned			nvarchar(max) output
as
begin
	Declare @SqlValue nvarchar(max)
	set @SqlValue = 'Select @Returned = 
	(
			SELECT  top 1 ''' + @DatabaseName + '.dbo.'' + KU.table_name  + ''.'' + column_name 
			FROM ' + @DatabaseName + '.INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
			INNER JOIN ' + @DatabaseName + '.INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
			ON TC.CONSTRAINT_TYPE = ''PRIMARY KEY'' AND
			TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME
			and ku.table_name= ''' + @TableName + '''
			ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION
	)
	'
	
	exec sp_executesql @SqlValue,N'@Returned nvarchar(max) output', @Returned output
end

Bu örneğimizde de veritabanındaki table constraints ve key column usage arasında bağlantı kurarak constrainttype ı Primary Key olan ve table name i istediğimiz tablonun adı olan sorguyu yazdık.

Bu procedure mizi de aşağıdaki kod ile test edebiliriz.

Declare @Result nvarchar(max)
exec sys_GetTablePrimaryKey 'VERITABANIADI', 'XtraNameSpaces',@Result output
Select @Result
Result : 
VeritabaniAdi.dbo.XtraNameSpaces.XtraNameSpaceId

Bu procedure u da biz select çekip istediğimiz kolonları belirttiğimiz sorguda Where kısmında primarykey değerleri eşleştirmesinde sorgu olarak kullanacağız.

Şimdiki işlemimiz dinamik sorgularımızı oluşturmaya geldi. 
sys_GetDifferentRowsQuery

ALTER proc [dbo].[sys_GetDifferentRowsQuery]
	@SourceDatabaseName		nvarchar(max),
	@TargetDatabaseName		nvarchar(max),
	@TableName				nvarchar(max),
	@ColumnType				tinyint,
	@Returned				nvarchar(max) output
as
begin
	declare @SourceSqlValue nvarchar(max)
	declare @TargetSqlValue nvarchar(max)
	declare @SqlValue nvarchar(max)

	declare @SourceColumns nvarchar(max)
	declare @SourcePrimaryKey nvarchar(max)
	
	declare @TargetColumns nvarchar(max)
	declare @TargetPrimaryKey nvarchar(max)

	exec dbo.sys_GetTableColumns @SourceDatabaseName,@TableName,@SourceColumns output
	exec dbo.sys_GetTablePrimaryKey @SourceDatabaseName, @TableName, @SourcePrimaryKey output

	exec dbo.sys_GetTableColumns @TargetDatabaseName,@TableName,@TargetColumns output
	exec dbo.sys_GetTablePrimaryKey @TargetDatabaseName, @TableName, @TargetPrimaryKey output

	Set @TargetSqlValue = 'Select ' + @TargetPrimaryKey + ' From ' + @TargetDatabaseName + '.dbo.' + @TableName

	if (@ColumnType = 1)
		Set @SourceSqlValue = 'Select ''' + @TableName + ''', ' + @SourcePrimaryKey + ' From ' + @SourceDatabaseName + '.dbo.' + @TableName
	else if (@ColumnType=2)
		Set @SourceSqlValue = 'Select ' + @SourcePrimaryKey + ' From ' + @SourceDatabaseName + '.dbo.' + @TableName
	else if (@ColumnType=3)
		Set @SourceSqlValue = 'Select ' + @SourceColumns + ' From ' + @SourceDatabaseName + '.dbo.' + @TableName

	Set @SqlValue = @SourceSqlValue + ' Where not ' + @SourcePrimaryKey + ' in (' +  @TargetSqlValue + ')'

	set @Returned = @SqlValue
end

Bu procedure miz de bize nihayi olarak birbirinden farklı kayıtları bulabileceğimiz sorgunun tamamını geri veriyor.
Yani Select * From a.dbo.XtraNameSpaces Where not a.dbo.XtraNameSpaces.XtraNameSpaceId in (Select XtraNameSpaceId From b.dbo.XtraNameSpaces) şeklinde Sql cümlesini dönecek. Henüz satırları geri döndürmüyoruz. 

Bu procedure ü inceleyecek olursak dışarıdan 5 adet parametre alıyor geriye Returned ile sonucu dönüyor. Bunlar : SourceDatabaseName kaynak veritabanımızın adı, TargetDatabaseName hedef veritabanımızın adı, TableName iki veritabanında bulunan tablonun adı columnttype ise geriye 3 case den oluşan bir sonuç dönüyoruz.

1. Kaynak veritabanındaki Tablo adı ve Primary Key Id leri,
2. Kaynak veritabanındaki sadece Primary Key Id leri
3. Kaynak veritabanındaki tüm kolonlarını

Hedef veritabanındaki tablo ile eşleşmeyen sonuçlarının sorgusunu dönüyoruz. 

Bu procedure de bir önceki oluşturduğumuz procedure ün içerisindeki output ile döndüğümüz değerleri burada oluşturduğumuz değişkenlere eşliyoruz. 

Bu procedure ü de aşağıdaki kod ile test edebiliriz. 

Declare @Result1 nvarchar(max)
Declare @Result2 nvarchar(max)
Declare @Result3 nvarchar(max)
exec sys_GetDifferentRowsQuery N'KaynakVeritabaniAdi', N'HedefVeritabaniAdi','XtraNameSpaces',1,@Result1 out
exec sys_GetDifferentRowsQuery N'KaynakVeritabaniAdi', N'HedefVeritabaniAdi','XtraNameSpaces',2,@Result2 out
exec sys_GetDifferentRowsQuery N'KaynakVeritabaniAdi', N'HedefVeritabaniAdi','XtraNameSpaces',3,@Result3 out
Select @Result1
Select @Result2
Select @Result3
Result1: 
Select 'XtraNameSpaces', KaynakVeritabaniAdi.dbo.XtraNameSpaces.XtraNameSpaceId From KaynakVeritabaniAdi.dbo.XtraNameSpaces Where not KaynakVeritabaniAdi.dbo.XtraNameSpaces.XtraNameSpaceId in (Select HedefVeritabaniAdi.dbo.XtraNameSpaces.XtraNameSpaceId From HedefVeritabaniAdi.dbo.XtraNameSpaces)

Result2:
Select KaynakVeritabaniAdi.dbo.XtraNameSpaces.XtraNameSpaceId From KaynakVeritabaniAdi.dbo.XtraNameSpaces Where not KaynakVeritabaniAdi.dbo.XtraNameSpaces.XtraNameSpaceId in (Select HedefVeritabaniAdi.dbo.XtraNameSpaces.XtraNameSpaceId From HedefVeritabaniAdi.dbo.XtraNameSpaces)


Result3:
Select XtraNameSpaceId, Type, Name, Caption, FullName, ParentXtraNameSpaceId From KaynakVeritabaniAdi.dbo.XtraNameSpaces Where not KaynakVeritabaniAdi.dbo.XtraNameSpaces.XtraNameSpaceId in (Select HedefVeritabaniAdi.dbo.XtraNameSpaces.XtraNameSpaceId From HedefVeritabaniAdi.dbo.XtraNameSpaces)

Sorgumuz için dinamik sql cümlelerimizi bu stored procedure miz ile artık alabiliyoruz. Şimdi sıra bir dış stored procedure müzü oluşturarak bu sql cümleleriyle sonuçları almaya geldi.

sys_GetDifferentRow

ALTER proc [dbo].[sys_GetDifferentRow]
	@SourceDatabaseName		nvarchar(max),
	@TargetDatabaseName		nvarchar(max),
	@TableName				nvarchar(max),
	@ColumnType				tinyint
as
begin
	Declare @SqlValue nvarchar(max)

	exec dbo.sys_GetDifferentRowsQuery @SourceDatabaseName, @TargetDatabaseName, @TableName, @ColumnType, @SqlValue out
	exec sp_executesql @SqlValue
end

Bu sorgumuzda diğer sorgumuz gibi dışarıdan kaynak veritabanı adı, hedef veritabanı adı tablo adı ve kolon tipi parametreleri alıyor. Bu sorgumuzda sys_GetDifferentRowsQuery procedure üne parametreleri göndererek oradan dışarıya çıkan sonucu @SqlValue değişkenine atıyor. Bu sonucu exec sp_executesql ile sorgunun sonucunu alabiliyoruz. 

Bu procedure ü de aşağıdaki kod ile test edebiliriz. 

exec sys_GetDifferentRow 'KaynakVeritabani', 'HedefVeritabani','XtraNameSpaces',1
exec sys_GetDifferentRow 'KaynakVeritabani', 'HedefVeritabani','XtraNameSpaces',2
exec sys_GetDifferentRow 'KaynakVeritabani', 'HedefVeritabani','XtraNameSpaces',3
Result1:

Result2:

Result2:

Sorgumuzun 3 farklı sonucunu görebiliyoruz. Bu sonuca göre Kaynak veritabanındaki XtraNameSpaces tablosunda olup Hedef veritabanındaki XtraNameSpaces tablosunda olmayan 2 adet kaydımız bulunuyor. 

Şimdi sıra bu 2 kaydı hedef veritabanında kaydını oluşturan stored procedure mizi yazmaya geldi.

sys_SetDifferentRow

create proc [dbo].[sys_SetDifferentRow]
	@SourceDatabaseName		nvarchar(max),
	@TargetDatabaseName		nvarchar(max),
	@TableName			nvarchar(max)
as
begin
	Declare @InsertSqlValue nvarchar(max)
	Declare @TargetColumns nvarchar(max)
	Declare @DifferentRowQuery nvarchar(max)
	begin try
		begin tran
			exec dbo.sys_GetTableColumns @TargetDatabaseName,@TableName,@TargetColumns output
			exec dbo.sys_GetDifferentRowsQuery @SourceDatabaseName, @TargetDatabaseName, @TableName, 3, @DifferentRowQuery output

			Set @InsertSqlValue = 'SET IDENTITY_INSERT ' + @TargetDatabaseName + '.dbo.' + @TableName + ' ON '
			set @InsertSqlValue += 'Insert Into ' + @TargetDatabaseName + '.dbo.' + @TableName + ' '
			set @InsertSqlValue += '( ' + @TargetColumns + ') '
			set @InsertSqlValue += @DifferentRowQuery
			set @InsertSqlValue += 'SET IDENTITY_INSERT ' + @TargetDatabaseName + '.dbo.' + @TableName + ' OFF '

			exec sp_executesql @InsertSqlValue
			commit tran
		end try
		begin catch
			rollback tran
			Select	ERROR_LINE(), 
					ERROR_NUMBER(), 
					ERROR_SEVERITY(), 
					ERROR_STATE(), 
					ERROR_MESSAGE()
		end catch
end

Bu procedure müzde SourceDatabaseName, TargetDatabaseName ve TableName parametreleri alıyor. Ardından içeride tanımlı 3 ayrı değişkenimiz bulunuyor. Bunlar targetColumns değişkeni hedef veritabanındaki tablonun kolonlarını daha önce oluşturduğumuz procedure dan alıyor. Bir diğeri DifferentRowQuery ise iki veritabanındaki tablolar arasında farklı olan kayıtların sql cümlesini önceki procedure den getiriyor. Son olarak InsertSqlValue değişkenimiz ise önce kayıt yapacağımız tablonun Identity insert özelliğini on a çekerek Identity kolonunda değişiklik yapabilmemizi sağlıyor. Ardından Insert Into ile targetdatabase ve table name e insert cümlesi ve buna bağlı kolonların listesini sorguda hazırlıyor. Son olarak DifferentRowQuery den gelen sonuçların tablename e yazılmasını sağlayıp Identity insert özelliğini tekrar off durumuna getiriyor. 

İşlemin başına begin try ile try catch ile hata oluşup oluşmadığını yakalıyoruz. Bir içeride ise begin tran ile transaction açarak işlemin hatasız sonuçlanmasına göre commit tran koduyla işlemin tamamlanmasını sağlıyoruz. İşlemimizde hata oluşursa rollback tran ile işlemi geri alıyoruz. Son olarak hataya düşerse geriye hata mesajlarını dönüyoruz. 

Bu procedure mizi de test edecek olursak

sys_SetDifferentRow1 'KaynakVeritabani','HedefVeritabani','XtraNameSpaces'
(2 row(s) affected)

İşlemimizi başarıyla tamamladık. Bir veritabanındaki kaydın aynısını diğer bir veritabanındaki tabloda kontrolünü sağlayıp insert işlemini gerçekleştirdik.

Aşağıda linkini vereceğim sql kodlarında burada anlatmaya fırsat bulamadığım bir konu daha ver. O da Insert işlemi sırasında o tabloya ait Trigger ları disable a çekip insert işlemi bittikten sonra tekrar Enable a çekiyor. Böylelikle toplu veri kayıtlarında triggerlar kapatılarak daha hızlı işlem yapmanızı sağlıyor.