I have a requirement for selecting json column stored as text and parse it in a result set. For that i have written a stored procedure which is working fine.
ALTER PROCEDURE dbo.sp_SerializeJsonFromTable
AS
Declare @json NVARCHAR(MAX)
Declare @Id int
Select *
Into #Temp
From TTAuditLog
CREATE TABLE #Temp1
(
Date_Time varchar(50),
Exch varchar(100),
Alias varchar(100),
Broker varchar(100),
Company varchar(100),
Trd_Status varchar(50) ,
Trd_Message varchar(max),
Action varchar(20),
B_S varchar(10),
O_C varchar(10),
C_P varchar(10),
Qty Float(50),
Product varchar(50),
Contract varchar(50),
Strike varchar(50),
Price Float(50),
Order_Type varchar(20),
TIF varchar(20),
Modifier varchar(20),
Trigger_Price Float(50),
TPrice_Type varchar(20),
TPrice_Qty Float(50),
Ticks_Away varchar(10),
Advanced varchar(50),
Disc_Qty Float(50),
Variance_pct Float(10),
Duration varchar(20),
Interval varchar(20),
Retries varchar(10),
Leftover_Act varchar(20),
Exec_Qty Float(50),
Work_Qty Float(50),
Cxl_Qty Float(50),
Legged_Qty Float(50),
Pending_Qty Float(50),
Undisclosed_Qty Float(50),
Acc_Number varchar(50),
Risk_Account varchar(50),
Exch_Mbr varchar(20),
Exch_Grp varchar(20),
Exch_Trd varchar(20),
Username varchar(50),
Exch_Cred varchar(50),
Trd_Mbr varchar(20),
Trd_Grp varchar(20),
Trd_ID varchar(20),
Acct varchar(20),
Give_Up varchar(20),
Cntr_Party varchar(50),
Exch_Time Time,
Exch_Date DateTime,
Time_Sent Time,
Src varchar(20),
Time_Proc varchar(20),
P_A varchar(10),
Owner varchar(20),
Order_Num varchar(20),
TT_Order_Key varchar(20),
Rel_Key varchar(20),
Parent_Key varchar(20),
Link_Type varchar(20),
SE_Server varchar(50),
IP_Address varchar(20),
FFT2 varchar(10),
FFT3 varchar(10),
FFT4 varchar(10),
FFT5 varchar(10),
FFT6 varchar(10),
UserTag varchar(20),
OrderTag varchar(20),
Dir_Elec_Access varchar(20),
Trading_Capacity varchar(20),
Liq_Prov varchar(20),
Cmdty_Der_Ind varchar(10),
Inv_Decision varchar(20),
Exec_Decision varchar(20),
Client varchar(20),
Start_Time Time,
Start_Date DateTime,
End_Time Time,
End_Date DateTime,
End_Behavior varchar(20),
TransID varchar(20),
Session_ID varchar(10),
Mon_Username varchar(20),
Callback_Rec varchar(10),
SeriesKey varchar(20),
Exch_Order_ID varchar(20),
Destination varchar(20),
FlowDel_Unit varchar(20),
Time_Rec varchar(20),
Order_Src_Hist varchar(20),
Last_Ord_Src varchar(20)
)
While (Select Count(*) From #Temp) > 0
Begin
Select Top 1 @Id = Id, @json = JsonValue From #Temp
--PRINT @json
--SET @json = 'N' + @json
--Declare @sql varchar(max)
INSERT INTO #Temp1 SELECT * FROM
OPENJSON (@json)
WITH (
Date_Time varchar(50) '$."Time"',
Exch varchar(50) '$."Exch"',
Alias varchar(100) '$."Alias"',
Broker varchar(100) '$."Broker"',
Company varchar(100) '$."Company"',
Trd_Status varchar(50) '$."Status"',
Trd_Message varchar(200) '$."Msg"',
Action varchar(20) '$."Action"',
B_S varchar(10) '$."B_S"',
O_C varchar(10) '$."O_C"',
C_P varchar(10) '$."C_P"',
Qty Float(50) '$."Qty"',
Product varchar(50) '$."Product"',
Contract varchar(50) '$."Contract"',
Strike varchar(50) '$."Strike"',
Price Float(50) '$."Price"',
Order_Type varchar(20) '$."OrderType"',
TIF varchar(20) '$."TIF"',
Modifier varchar(20) '$."Modifier"',
Trigger_Price Float(50) '$."TriggerPrice"',
TPrice_Type varchar(20) '$."TPriceType"',
TPrice_Qty Float(50) '$."TPriceQty"',
Ticks_Away varchar(10) '$."TicksAway"',
Advanced varchar(50) '$."Advanced"',
Disc_Qty Float(50) '$."DiscQty"',
Variance_pct Float(10) '$."Variance"',
Duration varchar(20) '$."Duration"',
Interval varchar(20) '$."Interval"',
Retries varchar(10) '$."Retries"',
Leftover_Act varchar(20) '$."LeftoverAction"',
Exec_Qty Float(50) '$."ExecQty"',
Work_Qty Float(50) '$."WorkQty"',
Cxl_Qty Float(50) '$." CxlQty"',
Legged_Qty Float(50) '$."LeggedQty"',
Pending_Qty Float(50) '$."PendingQty"',
Undisclosed_Qty Float(50) '$."UndisclosedQty"',
Acc_Number varchar(50) '$."AccountNumber"',
Risk_Account varchar(50) '$."RiskAccount"',
Exch_Mbr varchar(20) '$."ExchMbr"',
Exch_Grp varchar(20) '$."ExchGrp"',
Exch_Trd varchar(20) '$."ExchTrd"',
Username varchar(50) '$."Username"',
Exch_Cred varchar(50) '$."ExchCred"',
Trd_Mbr varchar(20) '$."TrdMbr"',
Trd_Grp varchar(20) '$."TrdGrp"',
Trd_ID varchar(20) '$."TrdID"',
Acct varchar(20) '$."Acct"',
Give_Up varchar(20) '$."GiveUp"',
Cntr_Party varchar(50) '$."CntrParty"',
Exch_Time Time '$."ExchTime"',
Exch_Date DateTime '$."ExchDate"',
Time_Sent Time '$."TimeSent"',
Src varchar(20) '$."Source"',
Time_Proc varchar(20) '$."TimeProcessed"',
P_A varchar(10) '$."P_A"',
Owner varchar(20) '$."Owner"',
Order_Num varchar(20) '$."OrderNumber"',
TT_Order_Key varchar(20) '$."TTOrderKey"',
Rel_Key varchar(20) '$."RelatedKey"',
Parent_Key varchar(20) '$."ParentKey"',
Link_Type varchar(20) '$."LinkType"',
SE_Server varchar(50) '$."SEServer"',
IP_Address varchar(20) '$."IPAddress"',
FFT2 varchar(10) '$."FFT2"',
FFT3 varchar(10) '$."FFT3"',
FFT4 varchar(10) '$."FFT4"',
FFT5 varchar(10) '$."FFT5"',
FFT6 varchar(10) '$."FFT6"',
UserTag varchar(20) '$."UserTag"',
OrderTag varchar(20) '$."OrderTag"',
Dir_Elec_Access varchar(20) '$."DirectElectronicAccess"',
Trading_Capacity varchar(20) '$."TradingCapacity"',
Liq_Prov varchar(20) '$."LiquidityProvision"',
Cmdty_Der_Ind varchar(10) '$."CommodityDerivativeIndicator"',
Inv_Decision varchar(20) '$."InvestmentDecision"',
Exec_Decision varchar(20) '$."ExecutionDecision"',
Client varchar(20) '$."Client"',
Start_Time Time '$."StartTime"',
Start_Date DateTime '$."StartDate"',
End_Time Time '$."EndTime"',
End_Date DateTime '$."EndDate"',
End_Behavior varchar(20) '$."EndBehavior"',
TransID varchar(20) '$."TransID"',
Session_ID varchar(10) '$."SessionID"',
Mon_Username varchar(20) '$."MonitoringUsername"',
Callback_Rec varchar(10) '$."CallbackReceived"',
SeriesKey varchar(20) '$."SeriesKey"',
Exch_Order_ID varchar(20) '$."ExchangeOrderID"',
Destination varchar(20) '$."Destination"',
FlowDel_Unit varchar(20) '$."FlowDeliveryUnit"',
Time_Rec varchar(20) '$."TimeReceived"',
Order_Src_Hist varchar(20) '$."OrderSourceHistory"',
Last_Ord_Src varchar(20) '$."LastOrderSource"'
)
Delete #Temp Where Id = @Id
End
SELECT * From #Temp1 order by Date_Time
Now I have a requirement wherein I can assign the field names inside with statement dynamically, rather than writing them specifically each one.
Please help.
sp_
prefix for your stored procedures. Microsoft has reserved that prefix for its own use (see Naming Stored Procedures), and you do run the risk of a name clash sometime in the future. It's also bad for your stored procedure performance. It's best to just simply avoidsp_
and use something else as a prefix - or no prefix at all!