JSON_Automated_stored_procedure
What is JSON:
JSON
stands for JavaScript Object Notation,
and is a way to store information in an organized, easy-to-access manner, "self-describing"
and easy to understand. It gives us a human-readable collection of data
that we can access in a really logical manner.
Why we need JSON:
JSON is a
lightweight data-interchange format and it is language independent. Many sites
are sharing data using JSON in addition to RSS feeds nowadays, and with good
reason: JSON feeds can be loaded asynchronously much more easily than
XML/RSS.
Format
of JSON string:
JSON
Example
|
{
"employees":[
{ "firstName":"John", "lastName":"Doe" }, { "firstName":"Anna", "lastName":"Smith"}, { "firstName":"Peter", "lastName":"Jones"} ]
}
|
You can
compare the above JSON string with the below XML data:
|
<employees>
<employee> <firstName>John</firstName> <lastName>Doe</lastName> </employee> <employee> <firstName>Anna</firstName> <lastName>Smith</lastName> </employee> <employee> <firstName>Peter</firstName> <lastName>Jones</lastName> </employee> </employees> |
JSON String Details:
Automated
“Stored Procedure” to generate JSON :
|
USE [lfo_adw]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Created By Jasmin A Sayyad.
--Created Date: 07-05-2015
Create procedure [dbo].[usp_get_JSON]
@schema_name varchar(250)='lfo_stage',
@table_name
varchar(250)='devry_implementation',
@Condition varchar(max)='isprocessed =0 and
client= ''DeVry'' and cast(report_date as date)=cast(''2010-02-24'' as
date)',
@ColumnFilter varchar(max)= 'isprocessed,client,company,vertical,program,report_date
',
@ResultColName
varchar(max)='company,vertical,program,client '
as
begin
DECLARE @registries_per_request
smallint = null
declare @json varchar(max),
@line varchar(max),
@columns nvarchar(max),
@sql nvarchar(max),
@columnNavigator varchar(50),
@DataType varchar(50),
@counter int,
@size varchar(10),
@DoubleQuote varchar(10)='',
@Split char(1)= ',',
@X xml
set @columns
= '''{''+'
Set @columns= @columns + 'CHAR(13) +
CHAR(10)+'
SELECT @X =
CONVERT(xml,'<root><s>' +
REPLACE(@ColumnFilter,@Split,'</s><s>') +
'</s></root>')
declare schemaCursor cursor for
select
column_name,Data_type
from
information_schema.columns
where
table_schema = @schema_name
and
table_name = @table_name
and
column_name not in(
SELECT
[Value] = T.c.value('.','varchar(20)')
FROM @X.nodes('/root/s') T(c))
open schemaCursor
select @counter=count(1)
from
information_schema.columns
where
table_schema = @schema_name
and
table_name = @table_name
and
column_name not in(
SELECT
[Value] = T.c.value('.','varchar(20)')
FROM @X.nodes('/root/s') T(c))
fetch next from schemaCursor into
@columnNavigator, @DataType
while @@fetch_status = 0
begin
set
@counter = @counter - 1
set
@columns = isnull(@columns,'')
+'+''"''+'+
--"
+''''+
isnull(@columnNavigator,'')+''''+''----- Column Name
+'+''"''+'+------"
+
+''':'''+''+ ------:
case
when @DataType='varchar' OR @DataType='nvarchar' OR @DataType='date' then
'+''"''+' else '+' end
----+'+''"''+'+-------"
+'isnull(convert(varchar,
' + isnull(@columnNavigator,'') + '),''"NULL"'') +
'''''+-----Column Value
case
when @DataType='varchar' OR @DataType='nvarchar' OR @DataType='date' then '+''"''+' else '+' end+
--+'+''"''+'+-------"
(Case
when @counter!=0 then ''+''',''' else '+' END)-------,
+
'+CHAR(13) + CHAR(10)'-------Enter
fetch
next from schemaCursor into @columnNavigator,@DataType
end
close
schemaCursor
deallocate
schemaCursor
set @columns
= isnull(@columns,'') + '+''}'''
Set @SQL ='Select
'+''+@ResultColName+','+ 'REPLACE('+@columns+',''""'',''"'')'
+ ' as JSON from '+ '['
+isnull(@schema_name,'') + '].[' + isnull(@table_name,'')+ ']'
if (isnull(@Condition,'')<>'')
BEGIN
Set @SQL=@SQL+' Where '+@Condition
END
--print @SQL
--select len(@SQL)
--select @SQL
exec sp_sqlexec @sql
END
|
Hope You will find this code helpful. Please provide your feedback in comment in case of any issue.
Comments
Post a Comment