SP - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Author

Topic

linda9898
Starting Member

28 Posts

Posted - 2007-10-01 : 04:18:58

Hi ,

I built a sp that receive Purchase order number and make some calculations and update it according to the results.
my problem is to call to this SP

what i want to do is to run for all POs in the table and make the calculation as :

select SP_Calculate po_number
from orders

or

exec SP_Calculate (select po_number from orders)

but i get an errors.
is anyone have any idea?
thanks
Linda

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 05:28:13

To call an SProc for several rows in a table you need to use a loop or a cursor.

Then for each row extract the columns into some @Variables, and then EXEC the Sproc.


DECLARE @po_number int,
        @RowCount int

SET @po_number = 0,   -- Start value
    @intRowCount = 1     -- For 1st iteration

WHILST @intRowCount > 0
BEGIN
    select TOP 1 @po_number = po_number
    from orders
    WHERE po_number > @po_number
    SELECT @intRowCount = @@ROWCOUNT

    IF @intRowCount > 0
    BEGIN
        EXEC SP_Calculate @po_number = @po_number
    END
END

Kristen

linda9898
Starting Member

28 Posts

Posted - 2007-10-01 : 10:02:19

Hi Kristen
thanks a lot. it realy helped.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources