Loop through a table - For Each Record in table, execute an SP 1

[DSect]

Hello -

I'm new to "looping" in MS SQL Server 2K and I have this question:

How can I loop through a database table, for each record in the table I need to execute a stored procedure and update a temporary table based on the SP results? I know how to do everything but the loop..

Here's an example:

Customer table has 100 records. Customer_Id is the PK.

I have a stored procedure that returns an integer when I give it a customer_id.

I can do: exec sp_MyCount 23

The SP will return an the customer ID & an integer (the value is 4, in this case for customer # 23)

So the results of exec sp_MyCount 23 would be

Customer_Id | SPValue
---------------------
23 | 4

It returns one row, two columns (it is the result of a SELECT from a TEMP table inside my SP).


I would like to run this stored procedure for each record in my customers table and insert data into a temp table.

Ideally, the end result (temporary table) would look like this:

SELECT Customer_Id, SPValue FROM #MyTemp

Customer_Id | SPValue
---------------------
1 | 0
2 | 5
3 | 3
4 | 2
5 | 10

Up to the last customer ID in my table.

I cannot for the life of me figure out how to do this simple task?!?

Also - How can I insert the value returned by the SP into my TEMP table.

So - Here's the summary: 2 Questions:

Question # 1:
If I execute an SP, how can I insert the values retuned from that into a temp. table and return the results?

Question # 2:
How can I loop the above operation FOR EACH record in my table?


I hope I'm being clear.. I thought the hard part was writing my SP that returns the INT based on some very funky stuff.. That wasn't too bad -

Looping through the Customers table and exec'ing the SP ON EACH RECORD is what I cannot figure out + I do not know how to insert the results of my SP into a TEMP table.

If someone could answer any one of my 2 questions that would be great. Both would be SUPERB!!

Thanks again!

 

[vongrunt]

Hm... user-defined function looks more suitable here:

select customerID, dbo.myFunction( customerID )
from myTable

One statement - all results.

 

[DSect]

Here's what I finally came up with, with other people's help..

von - You are probably right, but the solution posted below solves the 2 problems I'd had:

Problem 1 - How to loop through records on a table
Problem 2 - Dump the output of an SP to a table


-------------------------------------------------------
#1. How to LOOP through all records in a table using a cursor and using PRINT as proof-of-concept:

DECLARE @x int
DECLARE test_cursor CURSOR FOR
SELECT Customer_Id FROM tblCustomers
OPEN test_cursor
FETCH NEXT FROM test_cursor INTO @x
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @x
FETCH NEXT FROM test_cursor INTO @x
END
CLOSE test_cursor
DEALLOCATE test_cursor

#2. How to retrieve the OUTPUT param of a stored procedure from inside of another stored procedure (2 test procs):

CREATE PROCEDURE sp_Test_Output
@valIn int,
@valOut int OUT
AS
SET @valOut = @valIn + 100
GO

CREATE PROCEDURE sp_Get_Output
AS
DECLARE @SPOutput int
-- Hardcoded the #5 for testing purposes
EXEC sp_Test_Output 5, @SPOutput OUT
PRINT @SPOutput
GO

At the SQL query window:
exec sp_Get_output

Will print:
105

to the message window

 

[TysonLPrice]

Her's an example of looping without a cursor. I found it somewhere and modified it for myself.

SET NOCOUNT ON

-- declare all variables!

DECLARE     @iReturnCode       int,

            @iNextRowId        int,

            @iCurrentRowId     int,

            @iLoopControl      int,

            @vchCustomerName   nvarchar(255),

            @vchCustomerNmbr   nvarchar(10)

            @chProductNumber   nchar(30)

            

-- Initialize variables!

SELECT @iLoopControl = 1

SELECT @iNextRowId = MIN(iRowId)

FROM   CustomerTable

 

-- Make sure the table has data.

IF ISNULL(@iNextRowId,0) = 0

   BEGIN

            SELECT 'No data in found in table!'

            RETURN

   END

-- Retrieve the first row

SELECT           @iCurrentRowId   = iRowId,

                 @vchCustomerNmbr = vchCustomerNmbr,

                 @vchCustomerName = vchCustomerName

FROM             CustomerTable

WHERE            iRowId = @iNextRowId

 

-- start the main processing loop.

WHILE @iLoopControl = 1

   BEGIN

     -- This is where you perform your detailed row-by-row

     -- processing.     

     -- Reset looping variables.            

            SELECT   @iNextRowId = NULL            

            -- get the next iRowId

            SELECT   @iNextRowId = MIN(iRowId)

            FROM     CustomerTable

            WHERE    iRowId > @iCurrentRowId

            -- did we get a valid next row id?

            IF ISNULL(@iNextRowId,0) = 0

               BEGIN

                        BREAK

               END

            -- get the next row.

            SELECT  @iCurrentRowId =   iRowId,

                    @vchCustomerNmbr = vchCustomerNmbr,

                    @vchCustomerName = vchCustomerName

            FROM    CustomerTable

            WHERE   iRowId = @iNextRowId            

   END

RETURN

 

[SQLSister]

DSect, just so you understand that looping through a table is an extremely poor practice. It is very hard on both system and database resources. That is why a user-defined function was suggested.

If you have to loop through 1,000,000 records you send the code to the server 1,00,00,000 times. This should virtually never be done as there are other solutions which are faster in almost all cases.



Questions about posting. See faq183-874

 

[vongrunt]

I thought this would come to cursors and looping without cursors... is there any FAQ about this subject?

 

[DSect]

Yep - I did find it was poor performance.

The end solution involved a count on a "select distinct" q/ with unions.

Exec. time went from 8 seconds rto < 1