/**

* create procedure for get all fields from json

*

* Mark

*

* 2014-7-17 17:16:01

*

* */

USE QEO_Insurance

GO

IF OBJECT_ID('sp_getJSONFields') IS NOT NULL

DROP PROCEDURE sp_getJSONFields

GO

CREATE PROCEDURE sp_getJSONFields

@Json VARCHAR(MAX)

AS

BEGIN

SELECT @Json=REPLACE(@Json,'{','')

SELECT @Json=REPLACE(@Json,'}',',')

DECLARE @temp VARCHAR(100)

DECLARE @objName VARCHAR(30)

DECLARE @objValue VARCHAR(30)

DECLARE @fieldSql VARCHAR(MAX)

SET @fieldSql='select '

WHILE LEN(@Json)>0

BEGIN

    SELECT @temp=SUBSTRING(@Json,0,CHARINDEX(',',@Json,0))

    --PRINT @temp

    SELECT @Json=RIGHT(@Json,LEN(@Json)-LEN(@temp)-1)

    --PRINT @Json

    set @objName =left(@temp,CHARINDEX(':',@temp,0)-1)

    set @objValue =right(@temp,len(@temp)-CHARINDEX(':',@temp,0))

    --PRINT  @objName+'='+ @objValue+';'

    set @fieldSql=@fieldSql+REPLACE(@objValue,'"','''')+' as '+REPLACE(@objName,'"','')+','

    --PRINT '------------------'

END

SET @fieldSql=LEFT(@fieldSql,LEN(@fieldSql)-1)

--EXEC sp_executesql @fieldSql

EXEC (@fieldSql)

END

GO

EXEC sp_getJSONFields @json= '{"VIN_Invalid":"1","VIN_ID":"427658","Veh_TypeCode":"CTRK","Year":"2011","Make":"TOYOTA","Veh_Model":"TUNDRA","Body_CD":"PK"}'