February 16, 2016 at 9:30 pm
Hello,
I want to replace cusror used in the stored procedure by any other alternative solution.
Can anyone help please?
Here is the code as below -
Declare @JOBID As Int
Declare @TDQID As Int
Declare @MessageVal XML
Declare @ReprocessJOBQueue bit
Set @ReprocessJOBQueue = 0 /* False */
Declare Cur_JQH Cursor For
Select JQH.JobID, TDQ.ID
From JOB_QUEUE_HISTORY JQH, TRANSACTION_DATA_QUEUE TDQ
Where TDQ.ID = JQH.TransactionDataQueueID
And JQH.Result = 2 and JQH.Description = 'FlexNet_ZASN'
Open Cur_JQH
While 1 = 1
Begin
Fetch Cur_JQH Into @JOBID , @TDQID
If @@FETCH_STATUS = -1
Break
Declare @Facility As Varchar(50)
Declare @ProductNo As Varchar(50)
Set @MessageVal = (
Select top 1 Cast(tdq.Message as XML)
From dbo.TRANSACTION_DATA_QUEUE TDQ
Where TDQ.ID = @TDQID)
Select @MessageVal As MsgVal
SELECT pref.value('(InputName/text())[1]', 'varchar(50)') as IpName
, pref.value('(InputValue/text())[1]', 'varchar(50)') as IpVal
FROM @MessageVal.nodes('/FlexNet_ZASN/FIInvocationSynchronousEvent/Parameters/Inputs') AS MsgXML(pref)
Where pref.value('(InputName/text())[1]', 'varchar(50)') = 'ProductNo'
Declare Cur_Msg Cursor For
Select pref.value('(InputName/text())[1]', 'varchar(50)') as IpName
, pref.value('(InputValue/text())[1]', 'varchar(50)') as IpVal
From @MessageVal.nodes('/FlexNet_ZASN/FIInvocationSynchronousEvent/Parameters/Inputs') AS MsgXML(pref)
Open Cur_Msg
While 1 = 1
Begin
Declare @Ip_Name Varchar(200)
Declare @Ip_Value Varchar(200)
Fetch Cur_Msg Into @Ip_Name, @Ip_Value
If @@FETCH_STATUS = -1
Break
If @Ip_Name = 'PartnerPlantSystem'
Set @Facility = @Ip_Value
If @Ip_Name = 'ProductNo'
Set @ProductNo = @Ip_Value
If IsNull(@Facility,'') != '' And IsNull(@ProductNo,'') != ''
Begin
Declare @ProductID As Int
Set @ProductID = (Select ID From Product Where ProductNo = @ProductNo)
Update PRODUCT_FACILITY
Set Active = 1
Where Active = 0
And Facility = @Facility
And ProductID = @ProductID
Set @ReprocessJOBQueue = 1
End
End
Close Cur_Msg
Deallocate Cur_Msg
IF @ReprocessJOBQueue = 1
Begin
INSERT INTO JOB_QUEUE
(Name, Description, CreatedDate, Pool, Status, SynchronizationQueue, ActionTypeID, Parameters,TransactionDataQueueID,
Timeout, NumberOfExecutionAttempts, AttemptSleepDuration, ExecutedAttempts,NumberOfResubmits,
ResubmitGroup,OriginalJobID,CreatedOn,CreatedBy,Active)
SELECT JQH.Name, JQH.Description, GETUTCDATE(), JQH.Pool, 0 As Staus, JQH.SynchronizationQueue, JQH.ActionTypeID, JQH.Parameters,
JQH.TransactionDataQueueID,JQH.Timeout, JQH.NumberOfExecutionAttempts,JQH.AttemptSleepDuration, JQH.ExecutedAttempts, 0 AS NumberOfResubmits,
JQH.ResubmitGroup, JQH.JobID, GETUTCDATE(), 'ReprocesFlexNet_ZASN', 1
FROM JOB_QUEUE_HISTORY JQH
WHERE JQH.JobID=@JOBID
AND JQH.Result = 2
AND ActionTypeID = 8
AND NOT EXISTS(SELECT * FROM JOB_QUEUE_HISTORY A WHERE A.TransactionDataQueueID = JQH.TransactionDataQueueID AND A.Result = 1)
UPDATE JOB_QUEUE_HISTORY
SET Result = 1
WHERE Result = 2
AND JobID=@JOBID
AND ActionTypeID = 8
AND EXISTS(SELECT * FROM JOB_QUEUE_HISTORY A WHERE A.TransactionDataQueueID = TransactionDataQueueID AND A.Result = 1)
End
End
Close Cur_JQH
Deallocate Cur_JQH
February 16, 2016 at 9:54 pm
There is no reason you should need a cursor here or any other form of RBAR solution for this. Based on a quick look at what you're trying to do you just need to better understand how the XML nodes method works and CROSS APPLY. There's no reason, for example, that you need to process one record at a time from TRANSACTION_DATA_QUEUE. Instead I would dump that Message column into a temp table where Message is already the XML data type. Then you could replace code like this:
Set @MessageVal = (
Select top 1 Cast(tdq.Message as XML)
From dbo.TRANSACTION_DATA_QUEUE TDQ
Where TDQ.ID = @TDQID)
SELECT pref.value('(InputName/text())[1]', 'varchar(50)') as IpName
, pref.value('(InputValue/text())[1]', 'varchar(50)') as IpVal
FROM @MessageVal.nodes('/FlexNet_ZASN/FIInvocationSynchronousEvent/Parameters/Inputs') AS MsgXML(pref)
Where pref.value('(InputName/text())[1]', 'varchar(50)') = 'ProductNo'
With code that looks more like this:
SELECT
pref.value('(InputName/text())[1]', 'varchar(50)') as IpName,
pref.value('(InputValue/text())[1]', 'varchar(50)') as IpVal
From dbo.TRANSACTION_DATA_QUEUE TDQ
CROSS APPLY tdq.Message.nodes('/FlexNet_ZASN/FIInvocationSynchronousEvent/Parameters/Inputs') AS MsgXML(pref)
WHERE TDQ.ID = @TDQID
AND pref.value('(InputName/text())[1]', 'varchar(50)') = 'ProductNo'
On a side note: "Message" is a bad name for a column. Try to avoid using Reserved words for column names. Something like MessageTxt would be better.
-- Itzik Ben-Gan 2001
February 16, 2016 at 11:48 pm
I want to replace cursors in the queries.
Please provide me the code in which cursor is not used.
Thanks for the help.
February 17, 2016 at 2:38 am
PritamSalvi (2/16/2016)
I want to replace cursors in the queries.Please provide me the code in which cursor is not used.
Thanks for the help.
I believe that Alan did just that. Not a fully tested fully worked out solution, but a good starting point.
If you are still stuck, then please post the following:
1. The layout of all tables involved, as CREATE TABLE statements - please exclude columns that are irrelevant to the issue, but include all constraints and indexes on the remaining columns.
2. A few rows of sample data, as INSERT statements. Make sure to choose sample data that accurately shows what the code needs to do, in as few rows as possible. Also, test the CREATE TABLE and INSERT statements on an empty database on your system before posting.
3. The expected results from that sample data, in an easy-to-read format, and with an explanation of how these results relate to the input data.
4. The work you have done so far. Have you tried Alan's suggestion? What happened? What problems have you run into, what did you try to solve them, and where did you get stuck.
I am always happy to help you, by giving you a nudge that gets you over the bump in the road. I *can* also do your entire job for you, but then it's called consultancy and I get to write an invoice.;-)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply