Enes TAŞ

MS SQL'de JSON verileriyle çalışmak konulu makale serimizin son bölümüne gelmiş bulunmaktayız. Bu bölümde JSON verilerimizi STORED PROCEDURE (Saklı Yordam) kullanarak dinamik bir şekilde içeri aktarmayı inceleyeceğiz. Bu prosedür yardımıyla her tablonuza uygun JSON verisini içeri aktarabilir, farklı tablolar için ayrı ayrı aktarım sorguları yazmaktan kaçınmış olursunuz. 

Prosedürümüzün genel çalışma mantığını anlatarak başlayalım. Prosedürümüz dört parametre almaktadır.

1. parametrede JSON verimiz,
2. parametrede tablo adımız,
3. parametrede tekrarlı kayıtların oluşmasını engellemek için ve update işlemi sağlamak için JOIN işleminde kullanacağımız alan adı,
4. parametrede ise update işlemi de yapılsın mı yapılmasın mı bunu belirleyebildiğimiz bir parametre olacak. 

Bu prosedür bize verilen tablo ismi doğrultusunda o tabloya ait kolonları çekip, insert ve update cümlelerini hazırlayacak. Aynı zamanda OPENJSON fonksiyonu sonrasında kullandığımız JSON verimizi tablo şemamıza uygun hale getirecek olan WITH cümlesini de hazırlayacak. JOIN işleminde kullanacağımız parametre ise OPENJSON fonksiyonundan dönen tablomuz ile veritabanındaki tablomuzu hangi alanla bağlayacağımızı belirten cümlemizi hazırlayacak. 

Prosedürümüzün CREATE sorgusu aşağıdaki şekildedir. Gerekli açıklamaları prosedür içerisinde belirttim. Anlaşılmayan noktalar için yorum üzerinden veya özel olarak iletişim sayfasından bana ulaşabilirsiniz. 

CREATE PROC IMPORT_FROM_JSON
@JSON NVARCHAR(MAX), /* JSON VERİMİZ */
@TABLE_NAME NVARCHAR(100) /* TABLO ADIMIZ */,
@JOIN_COLUMN NVARCHAR(100)=NULL /* JOIN SAĞLANACAK ALAN */,
@UPDATE BIT=0 /* VAR OLAN VERİLER UPDATE EDİLSİN Mİ (0 HAYIR, 1 EVET) */
AS
BEGIN

DECLARE @WITH_CLAUSE NVARCHAR(MAX)=N'', /* OPENJSON WITH CÜMLESİNİ OLUŞTURMAK İÇİN KULLANILACAK */ 
		@SQL_QUERY NVARCHAR(MAX)=N'' /* EXECUTE EDECEĞİMİZ INSERT VE UPDATE CÜMLELERİ İÇİN KULLANILACAK */,
		@COLUMNS NVARCHAR(MAX)=N'' /* SELECT CÜMLESİNDEKİ KOLONLAR İÇİN VE UPDATE CÜMLESİNDEKİ KOLON SET İŞLEMLERİ İÇİN KULLANILACAK */,
		@INSERT_COLUMNS NVARCHAR(MAX) =N'' /* INSERT CÜMLESİNDEKİ KOLONLAR İÇİN KULLANILACAK */;

/* TABLOMUZA AİT KOLONLARI VERİ TİPLERİ VE VERİ TİPİ ÖZELLİKLERİ İLE BERABER ÇEKİYORUZ VE TEMP TABLOYA EKLİYORUZ */
SELECT  
	c.name as ColumnName, /* KOLON ADI*/
	t.Name as DataType,  /* VERİ TİPİ */
	CAST(c.max_length as nvarchar) as MaxLength, /* MAX. KARAKTER UZUNLUĞU */
	CAST(c.precision as nvarchar) as Precision, /* DECIMAL ALANLAR İÇİN UZUNLUK */
	CAST(c.scale as nvarchar) Scale /* DECIMAL ALANLAR İÇİN VİRGÜLDEN SONRAKİ HANE UZUNLUĞU */
INTO #tempColumns
FROM sys.columns c /* KOLONLARIN TUTULDUĞU TABLO */
INNER JOIN sys.types t ON c.user_type_id = t.user_type_id /* VERİ TİPLERİNİN TUTULDUĞU TABLO */
LEFT JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id /* KOLON İNDEXLERİNİN TUTULDUĞU TABLO */
LEFT JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id /* İNDEXLERİN TUTULDUĞU TABLO */
WHERE c.object_id = OBJECT_ID(@TABLE_NAME) and ISNULL(c.is_identity, 0)=0 /* HANGİ TABLONUN KOLONLARI ÇEKECEĞİMİZİ BELİRTİYORUZ VE OTOMATİK ARTAN OLAN ALANIMIZI ÇEKMİYORUZ */
-- AND ISNULL(i.is_primary_key,0)=0  /* EĞER PRIMARY KEY OLAN ALANI DA DAHİL ETMEK İSTEMİYORSANIZ BU BÖLÜMÜ AÇABİLİRSİNİZ */

/* INSERT CÜMLESİNDE KULLANMAK İÇİN KOLONLARIMIZI VİRGÜLLE AYIRACAK ŞEKİLDE YAN YANA YAZIYORUZ */
SELECT @INSERT_COLUMNS += ColumnName + ',' FROM #tempColumns

/* SON KOLONDAN SONRA Kİ FAZLA VİRGÜLÜ TEMİZLEMEK İÇİN SUBSTRING FONKSİYONUNU KULLANIYORUZ */
SET @INSERT_COLUMNS = SUBSTRING(@INSERT_COLUMNS,1, LEN(@INSERT_COLUMNS)-1)


/* SELECT CÜMLESİNDE KULLANMAK İÇİN KOLONLARIMIZI VİRGÜLLE AYIRACAK ŞEKİLDE YAN YANA YAZIYORUZ */
SELECT @COLUMNS += 'J.' + ColumnName + ',' FROM #tempColumns

/* SON KOLONDAN SONRA Kİ FAZLA VİRGÜLÜ TEMİZLEMEK İÇİN SUBSTRING FONKSİYONUNU KULLANIYORUZ */
SET @COLUMNS = SUBSTRING(@COLUMNS,1, LEN(@COLUMNS)-1)

/* OPENJSON WITH CÜMLESİNİ OLUŞTURUYORUZ, 
EĞER VERİ TİPLERİ CHAR, VARCHAR, NVARCHAR VS İSE MAXLENGTH BİLGİSİNİ, 
DECIMAL İSE PRECISION VE SCALE BİLGİLERİNİ DE CASE WHEN YARDIMIYLA BELİRTİYORUZ */
SELECT @WITH_CLAUSE += ColumnName + ' ' + DataType + 
(CASE
 WHEN DataType LIKE '%char%' THEN '(' + MaxLength  + ')' 
 WHEN DataType LIKE '%decimal%' THEN '(' + Precision + ',' + Scale + ')' 
 ELSE '' END) + ' ''$.'+ColumnName + '''' + ',' FROM #tempColumns

/* SON KOLONDAN SONRA Kİ FAZLA VİRGÜLÜ TEMİZLEMEK İÇİN SUBSTRING FONKSİYONUNU KULLANIYORUZ */
SET @WITH_CLAUSE = SUBSTRING(@WITH_CLAUSE,1,LEN(@WITH_CLAUSE)-1)

/* EXECUTE EDECEĞIMIZ INSERT CÜMLESİNİ OLUŞTURUYORUZ */
SET @SQL_QUERY = N'DECLARE @JSON NVARCHAR(MAX)=N''' + @JSON + ''' ';
SET @SQL_QUERY += N'INSERT INTO ' + @TABLE_NAME + '(' + @INSERT_COLUMNS + ') '
SET @SQL_QUERY += N'SELECT DISTINCT ' + @COLUMNS + ' FROM OPENJSON(@JSON) WITH (' + @WITH_CLAUSE + ') AS J';

/* EĞER @JOIN_COLUMN PARAMETRESİ BELİRTİLMİŞSE BELİRTİLEN ALANA GÖRE JOIN İŞLEMİ SAĞLAYARAK TEKRARLI KAYITLARIN INSERT EDİLMEMESİNİ SAĞLIYORUZ */
IF(NULLIF(@JOIN_COLUMN,'') IS NOT NULL)
BEGIN
	SET @SQL_QUERY += N' LEFT JOIN ' + @TABLE_NAME + ' AS T ON T.' + @JOIN_COLUMN + '=J.' + @JOIN_COLUMN;
	SET @SQL_QUERY += N' WHERE T.' + @JOIN_COLUMN + ' IS NULL ';
END

/* INSERT CUMLEMİZİ EXECUTE EDIYORUZ */
EXEC(@SQL_QUERY);

/* INSERT SORGUSUNUN ÇIKTISINI GÖRMEK İSTERSENİZ BU BÖLÜMÜ AÇABİLİRSİNİZ */
--PRINT @SQL_QUERY;

/* @JOIN_COLUMN PARAMETRESİ VAR İSE VE @UPDATE PARAMETRESİ 1 İSE UPDATE SORGUSUNU HAZIRLIYORUZ */
IF(NULLIF(@JOIN_COLUMN,'') IS NOT NULL AND @UPDATE=1)
BEGIN
	SET @COLUMNS=N'';

	/* UPDATE SORGUSU İÇİN KOLON SET İŞLEMLERİNİN YAPILACAĞI CÜMLEYİ HAZIRLIYORUZ */
	SELECT @COLUMNS += 'T.' + ColumnName + '=J.' + ColumnName + ',' FROM #tempColumns; 
	
	/* SON KOLONDAN SONRA Kİ FAZLA VİRGÜLÜ TEMİZLEMEK İÇİN SUBSTRING FONKSİYONUNU KULLANIYORUZ */
	SET @COLUMNS = SUBSTRING(@COLUMNS,1, LEN(@COLUMNS)-1)

	/* EXECUTE EDECEĞİMİZ UPDATE CÜMLESİNİ HAZIRLIYORUZ */
	SET @SQL_QUERY = N'DECLARE @JSON NVARCHAR(MAX)=N''' + @JSON + ''' ';
	SET @SQL_QUERY += N' UPDATE T SET ' + @COLUMNS + ' FROM ' + @TABLE_NAME +' AS T ';
	SET @SQL_QUERY += N' INNER JOIN OPENJSON(@JSON) WITH (' + @WITH_CLAUSE + ') AS J ';
	SET @SQL_QUERY += N' ON T.'+@JOIN_COLUMN + '=J.' + @JOIN_COLUMN ;

	/* UPDATE CUMLEMİZİ EXECUTE EDIYORUZ */
	EXEC(@SQL_QUERY);

	/* UPDATE SORGUSUNUN ÇIKTISINI GÖRMEK İSTERSENİZ BU BÖLÜMÜ AÇABİLİRSİNİZ */
	--PRINT @SQL_QUERY;
END

/* TEMP TABLOMUZU DROP EDİYORUZ */
DROP TABLE #tempColumns

END