I have stored procedure as below, I am trying to use a column value as function ie dbo.SfnParseJSON(@formData) and other column values as other column values. But for some reason, Store Procedure is taking too long to run. After getting these values I need to join to other tables that’s the next steps, could you please help is there any different alternates for this, any help would be very very helpful.
<code>ALTER PROCEDURE [dbo].[SSRSAgreements]
@area varchar(50)
AS
BEGIN
declare @jsonName_EmergingBroker_DVBE NVARCHAR(500) = 'DVBE'
declare @jsonName_EmergingBroker_Minority NVARCHAR(500) = 'Minority'
declare @jsonName_EmergingBroker_Women NVARCHAR(500) = 'Women'
declare @jsonName_EmergingBroker_NotApplicable NVARCHAR(500) = 'NotApplicable'
declare @formDataTable table(
tempid int,
FormId uniqueidentifier not null,
CoreId uniqueidentifier,
FormData nvarchar(max)
)
insert into @formDataTable
select tempid=ROW_NUMBER() OVER(ORDER BY(SELECT NULL)),
FormId=FormId, CoreId=CoreId, FormData=FormData from tbForm
DECLARE @tempHierarhy TABLE(
Element_ID INT NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */
SequenceNo [int] NULL, /* the place in the sequence for the element */
Parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
[Object_ID] INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
[Name] NVARCHAR(2000),/* the name of the object, null if it hasn't got one */
StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */
ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/,
FormId uniqueidentifier not null,
CoreId uniqueidentifier,
PRIMARY KEY (Element_ID)
)
DECLARE @MaxTempId int;
DECLARE @CurrentTempId int;
SELECT @MaxTempId = MAX(TempId) from @formDataTable
SELECT @CurrentTempId = MIN(TempId) from @formDataTable
while @CurrentTempId <= @MaxTempId
begin
declare @FormId uniqueidentifier=null
declare @CoreId uniqueidentifier=null
declare @formData nvarchar(max)=null
select @FormId=FormId, @CoreId=CoreId, @formData=formData from @formDataTable where tempid=@CurrentTempId
INSERT INTO @tempHierarhy
Select *, @FormId, @CoreId from dbo.SfnParseJSON(@formData)
-- Increment list of forms identifier
set @CurrentTempId = @CurrentTempId + 1
end
select * from @tempHierarhy
end
</code>
<code>ALTER PROCEDURE [dbo].[SSRSAgreements]
@area varchar(50)
AS
BEGIN
declare @jsonName_EmergingBroker_DVBE NVARCHAR(500) = 'DVBE'
declare @jsonName_EmergingBroker_Minority NVARCHAR(500) = 'Minority'
declare @jsonName_EmergingBroker_Women NVARCHAR(500) = 'Women'
declare @jsonName_EmergingBroker_NotApplicable NVARCHAR(500) = 'NotApplicable'
declare @formDataTable table(
tempid int,
FormId uniqueidentifier not null,
CoreId uniqueidentifier,
FormData nvarchar(max)
)
insert into @formDataTable
select tempid=ROW_NUMBER() OVER(ORDER BY(SELECT NULL)),
FormId=FormId, CoreId=CoreId, FormData=FormData from tbForm
DECLARE @tempHierarhy TABLE(
Element_ID INT NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */
SequenceNo [int] NULL, /* the place in the sequence for the element */
Parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
[Object_ID] INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
[Name] NVARCHAR(2000),/* the name of the object, null if it hasn't got one */
StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */
ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/,
FormId uniqueidentifier not null,
CoreId uniqueidentifier,
PRIMARY KEY (Element_ID)
)
DECLARE @MaxTempId int;
DECLARE @CurrentTempId int;
SELECT @MaxTempId = MAX(TempId) from @formDataTable
SELECT @CurrentTempId = MIN(TempId) from @formDataTable
while @CurrentTempId <= @MaxTempId
begin
declare @FormId uniqueidentifier=null
declare @CoreId uniqueidentifier=null
declare @formData nvarchar(max)=null
select @FormId=FormId, @CoreId=CoreId, @formData=formData from @formDataTable where tempid=@CurrentTempId
INSERT INTO @tempHierarhy
Select *, @FormId, @CoreId from dbo.SfnParseJSON(@formData)
-- Increment list of forms identifier
set @CurrentTempId = @CurrentTempId + 1
end
select * from @tempHierarhy
end
</code>
ALTER PROCEDURE [dbo].[SSRSAgreements]
@area varchar(50)
AS
BEGIN
declare @jsonName_EmergingBroker_DVBE NVARCHAR(500) = 'DVBE'
declare @jsonName_EmergingBroker_Minority NVARCHAR(500) = 'Minority'
declare @jsonName_EmergingBroker_Women NVARCHAR(500) = 'Women'
declare @jsonName_EmergingBroker_NotApplicable NVARCHAR(500) = 'NotApplicable'
declare @formDataTable table(
tempid int,
FormId uniqueidentifier not null,
CoreId uniqueidentifier,
FormData nvarchar(max)
)
insert into @formDataTable
select tempid=ROW_NUMBER() OVER(ORDER BY(SELECT NULL)),
FormId=FormId, CoreId=CoreId, FormData=FormData from tbForm
DECLARE @tempHierarhy TABLE(
Element_ID INT NOT NULL, /* internal surrogate primary key gives the order of parsing and the list order */
SequenceNo [int] NULL, /* the place in the sequence for the element */
Parent_ID INT,/* if the element has a parent then it is in this column. The document is the ultimate parent, so you can get the structure from recursing from the document */
[Object_ID] INT,/* each list or object has an object id. This ties all elements to a parent. Lists are treated as objects here */
[Name] NVARCHAR(2000),/* the name of the object, null if it hasn't got one */
StringValue NVARCHAR(MAX) NOT NULL,/*the string representation of the value of the element. */
ValueType VARCHAR(10) NOT null /* the declared type of the value represented as a string in StringValue*/,
FormId uniqueidentifier not null,
CoreId uniqueidentifier,
PRIMARY KEY (Element_ID)
)
DECLARE @MaxTempId int;
DECLARE @CurrentTempId int;
SELECT @MaxTempId = MAX(TempId) from @formDataTable
SELECT @CurrentTempId = MIN(TempId) from @formDataTable
while @CurrentTempId <= @MaxTempId
begin
declare @FormId uniqueidentifier=null
declare @CoreId uniqueidentifier=null
declare @formData nvarchar(max)=null
select @FormId=FormId, @CoreId=CoreId, @formData=formData from @formDataTable where tempid=@CurrentTempId
INSERT INTO @tempHierarhy
Select *, @FormId, @CoreId from dbo.SfnParseJSON(@formData)
-- Increment list of forms identifier
set @CurrentTempId = @CurrentTempId + 1
end
select * from @tempHierarhy
end