SQL - Looping a stored procedure

~J~ · 19 Jan 2006 at 15:26

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

happytechie · 19 Jan 2006 at 15:56

select mySP @intYear = 2005, @intPeriod = 5, costcentre
from costCentretable;

HT

~J~ · 19 Jan 2006 at 16:04

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

happytechie · 19 Jan 2006 at 16:46

gah, sorry my brain hurts today

select sp_name(column name) from table should return you whatever the sp returns for each row in the table I think

HT

arty · 19 Jan 2006 at 20:47

Could you use a cursor? I think that's how I'd do it.

arty

oneilldo · 19 Jan 2006 at 21:17

Create another Stored procedure that references the Sp you have created.

Then in this second stored proc do the following:

NB: SQL will be slightly wrong as haven't got MSSQL here to test

CREATE PROC RunThroughAllCostCentres
AS
DECLARE @intCostCentre INT

DECLARE costCentreCursor CURSOR FOR
SELECT DISTINCT costCentre
FROM costCentreTable

FETCH NEXT FROM costCentreCursor INTO @intCostCentre

WHILE @@FETCHSTATUS =0
BEGIN
EXEC SP_EXECUTESQL 'SpNameAlreadyCreated 2005, 5,''' + @intCostCentre + ''''
FETCH NEXT FROM costCentreCursor INTO @intCostCentre
END
CLOSE costCentreCursor
DEALLOCATE costCentreCursor
GO

Something like above should give you what you want

~J~ · 20 Jan 2006 at 10:23

@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.

roboffer · 20 Jan 2006 at 10:47

Cursors

~J~ · 20 Jan 2006 at 16:17

roboffer said:
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

oneilldo · 21 Jan 2006 at 20:29

Ahh I must start to do something else instead of SQL at work from now on, if I can remember syntax almost exaxctly for cursors whilst typing into OcUK forums then i really need to get out more