SQL - Looping a stored procedure

[~J~]

Currently using MS SQL2000 and have a stored procedure that accepts 3 parameters...

@intYear,
@intPeriod,
@intCostCentre.

I've a table that is full of costcentre nominal codes, and want to run the stored procedure for each cost centre in the table.

For example...

SELECT mySP @intYear=2005, @intPeriod=5, @intCostCentre=SELECT costCentre FROM costCentreTable

Obviously that doesn't work but I'm sure I've read somewhere about using the IN statement to achieve this.

Any ideas?

TIA

 

[~J~]

Thanks. But that wouldn't actually run the stored procedure as it's not going through each row in the costcentre table.

Even if I could parse the command into a string, I could run it dynamically.

Was hoping to finish at 16:00 today too

 

[~J~]

@oneilldo - PERFECT!!!!! Cheers mate, just had to change @@FETCHSTATUS to include an underscore, but other than that spot on!

Thanks HT adn arty too for your help, much appreciated.

 

[~J~]

Cursors

Yeah, that's a downside, but I'll rework on that over the weekend to get rid, it was the 'logic' I wanted as my trusty old faithful SQL cookbook is 300 miles away