Hi ,
I need to create a loop in sequence job,in which the loop exit depends upon the value a of store procedure.
Store procedure output will be the count of the records from a table ,checking a column value.
If the store procedure value is '1' then i should wait for 6o seconds and again call the store procedure. This should continue till the output of the store procedure is '0'. Once i get '0' i should exit the loop and start another job.
Please any ideas of doing it.
Thanks.
Creating loop in Sequence
Moderators: chulett, rschirm, roy
Pretty much exactly like you've posted. What have you tried so far? Have you built looping Sequence jobs before or is this your first attempt at them?
The checking part should be straight-forward, for a '1' return continue the loop, for a '0' branch outside the loop, bypassing the End Loop stage to whatever steps you need to do once the loop completes.
The checking part should be straight-forward, for a '1' return continue the loop, for a '0' branch outside the loop, bypassing the End Loop stage to whatever steps you need to do once the loop completes.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
how should i check for return value from storeprocedure.
As i am getting a row value as 1 or 0 from storeprocedure.
storeprocedure:
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR
SELECT COUNT(*) AS CountOfDependencies
FROM
SCH.TABLE1
WHERE
YEAR = @YEAR_TYPE
AND TYPE_ID = 2 -- Updates
AND STATUS_ID IN (4, 5); -- Dispatched or Processing
OPEN c1;
END
output from storeprocedure :
COUNTOFDEPENDENCIES
-----------------------------
0 or 1
in the job_activity ,I am using Storeprocedure stage setting cursoroutput option and writing the value to a file.
As i am getting a row value as 1 or 0 from storeprocedure.
storeprocedure:
BEGIN
DECLARE c1 CURSOR WITH RETURN FOR
SELECT COUNT(*) AS CountOfDependencies
FROM
SCH.TABLE1
WHERE
YEAR = @YEAR_TYPE
AND TYPE_ID = 2 -- Updates
AND STATUS_ID IN (4, 5); -- Dispatched or Processing
OPEN c1;
END
output from storeprocedure :
COUNTOFDEPENDENCIES
-----------------------------
0 or 1
in the job_activity ,I am using Storeprocedure stage setting cursoroutput option and writing the value to a file.
If you are writing the value to a file, you'll need something to 'read' that file and then make a decision based on what that returns. Typically, that would be an Execute Command stage that does a cat on the file and then your triggers can check the $ReturnValue (I do believe) from the stage to decide what to do. Gets a little trickier than it sounds, though.
Far easier to use a Server job to call the proc and then write the value to USERSTATUS using the technique posted here. An exact search for SetUserStatus should turn up those conversations. If you go that route, the triggers after the Job Activity can automagically use $UserStatus to branch appropriately without an 'extra' step.
Far easier to use a Server job to call the proc and then write the value to USERSTATUS using the technique posted here. An exact search for SetUserStatus should turn up those conversations. If you go that route, the triggers after the Job Activity can automagically use $UserStatus to branch appropriately without an 'extra' step.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers