Execute next step based on Stored Procedure output

Question

Execute next step based on Stored Procedure output

AVB

SSChampion

Points: 11418
More actions
March 2, 2010 at 7:44 am

#211758

SQL 2000.....
I'm trying to conditionally execute an "Execute SQL Task" step from the previous "Execute SQL Task" based on the output of the proc in the first step.
The first step fires a proc that kicks out an Output Param. I've set this output param to a Global Variable (not sure if I'm on the right track). If the output parameter is a paticular value I want to execute the next step.
I thought using a global variable might be the right way to tackle this but I don't see anywhere in the Workflow properties that I can sepicify something like this. Any ideas? TIA.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

PaVeRa22 SSCommitted Points: 1965 More actions · Answer 1

Dear AVB,

Based on the first sql task value, you can enable and disable the next task, to do that use a activex script task in between and write VBA code to disable\enable next task.

Reg

RB

dbuendiab SSC Eights! Points: 993 More actions · Answer 2

I 'd suggest you to try two approaches:

1.- As someone else states, use a VBScript task to write the conditional code. In this case, you can set two green work-flow arrows for that task, and set the NextStep.Disable = False for the one you want to seek (and True for the other).

2.- Not so elegant, but you can also raise an error based on the output parameter of the former SP, and set two work-flow arrows, one for the task error case (the red one), and the other (green) for the ok case.

Diego Buendia

Barcelona, Spain

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

This might help: http://www.sqldts.com/218.aspx

Multiple execution paths shown, using scripting as suggested above.

AVB SSChampion Points: 11418 More actions · Answer 4

Rock on!

Thanks for the replies and the link. SSC to the rescue (again)!

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 5

If you come up with something interesting, consider dropping an article here. Still some people using DTS.