Solved

Loop through each record in table - Execute an SP for each record, insert results of SP into TEMP table.

Posted on 2004-09-30
23
1,509 Views
Last Modified: 2007-12-19
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 the results of the SP into a temp table. The last step would be to SELECT from the TEMP table.

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

I would run:

exec sp_GetInfo

Returned Recordset / Table looks like:

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!
0
Comment
Question by:D-Sect
  • 6
  • 5
  • 3
  • +5
23 Comments
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12194691
#2: sp_MSforeachtable

Example:

sp_MSforeachtable 'PRINT ''?'''

will execute "PRINT <tableName>" for each table
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12194734
>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..

If you modify your stored procedure to update a global temporary table (##TableName instead of #TableName), and if your stored procedure takes a table name as it's input, then you can do it this way:

sp_MSforeachtable 'EXEC sp_Test ''?'''
0
 
LVL 34

Expert Comment

by:arbert
ID: 12194816
Not sure what's included in your stored proc, but a good possibility might be to create a user defined function that returns your "integer value" with your select statement.....

Post your proc...
0
 
LVL 34

Expert Comment

by:arbert
ID: 12194866
You did want to loop through records right?  spMSForeachtable will loop through tables.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12195036
Worst case you will have to use a cursor.
0
 
LVL 69

Expert Comment

by:ScottPletcher
ID: 12195061
But your requirements don't read like you really need a cursor (and cursors are *very* inefficient, so should be avoided if possible).  You can use an INSERT to do a lookup to a temp table for all rows at once, with some kind of join, rather than having to pass every value to a SP.  Please post the code in the SP if you want a possible alternative solution.
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12195172
>spMSForeachtable will loop through tables.
Duh. I'll go back to bed now...
0
 
LVL 6

Expert Comment

by:gwalkeriq
ID: 12195195
However, you should learn how to use a cursor because they can be most handy, and they are not horrible overhead when the working set is relatively small and quickly processed.

Sometimes If you have a big table, updating each row sometimes you may want to use a technique that prevents a single update statement from processing thousands or millions of rows. Create a ProcessRange command that does the dirty work for a specific range, then call ProcessRange as needed (with different range parameters), untill everything is processed.

For a simple example, assume you want to process based on last name and you know all last names start with A-Z

exec ProcessRange('A');
exec ProcessRange('B');
...
exec ProcessRange('Z');

Obviously, ProcessRange could be called in a loop, etc.

Reason this can be an advantage is that you can avoid a complete table lock. Overall processing time is increased, but database stays up better during the execution of the mass update. Exact details will vary from case to case.





0
 

Author Comment

by:D-Sect
ID: 12195746
** Please read the questions @ the bottom ** I'd revised them to narrow the focus. What I'm trying to do here is very simple, I know, but I do not know the syntax and answering the specific questions @ the bottom will show me SYNTAX and FLOW needed to do this and other stuff!!

I'm not sure where sp_ForEachTable is coming into this from.. I need to loop though one table - all records. According to docs, sp_ForEachTable is for each table Am I wrong??.
I need to simply LOOP through only ONE table. Much like you would Do..While on a recordset until is was EOF.. In fact EXACTLY like Do While on a recordset.

My Stored procedure (sp_MyCount) Checks the existence of related records in about 17 related tables. It returns the # of tables that have related records for a customer_id.

This is for a survey (the web side that views results) - Customers have answered a lot of questions, their answers stored in multiple tables that relate back to Customer_Id / Customer Table - one table for each "section" of the survery.

I'm TRYING to get a list of customers who have answered X number of sections, so that I can exclude people that have not answered ANY sections and include customers that have answered AT LEAST one section (aka - the customer has at least 1 related table with information in it.

I'm so confused?? If this was normal ASP / VB , etc code and my Customers table was a recordset and my SP was a function, I'd do something like this:

Do While Not rs.Customers.EOF
  Debug.Print sp_MyView(rs(0) & " - " & rs(1))
Loop

That would print me a list like:
x - y where X is the Customer_Id (first column from my SP) and Y is the # of tables for that customer ID (the 2nd column from my SP)

1 - 0
2 - 4
3 - 2
4 - 9


Let's break it down into manageable pieces that will help me understand the technique(s):
**** Please POST CODE to answer these questions as they will help me understand the concepts & syntax!!!!! ****

1. How do I loop through ALL records in a table (tblCustomers) in a stored procedure?? For each record I want to PRINT the VALUE of the current record's first column to the SQL Profiler message window - Then, move to the next record - Repeat until no more records.  - This will help me understand LOOPING in the SP. Please show me code for this exact scenario. I'm  using PRINT to make it simple to explain..

2. How do I insert the RECORDS returned from a stored procedure into a table, ANY table?? Say my SP is named sp_MyCount.. sp_MyCount accepts an INT parameter. When executed it returns ONE ROW, TWO COLUMNS. I need to insert the results of this into a table called tblMyTable which has 2 columns. Please show CODE for how to do that exact scenario..

PLEASE POST THE CODE TO SOLVE THE QUESTIONS POSTED ABOVE  ^^^^^  !! It should only be like MAX 10 lines for each of those 2 questions.

I know they are simple and do not relate to my project, but they do relate and they aren't simple when you do not understand the syntax at all..  


Thanks!

Let me know if you need any clarification..  Again - Focus on the 2 new questions.. I am feeling like this has gotten waaaay off-course for something that is routine.
0
 
LVL 5

Accepted Solution

by:
ajitanand earned 460 total points
ID: 12197432
The easiest solution here is to go for Cursors [these are like recordsets in VB/ASP]

Source Table - abc (x int, y int) -> We will loop through this table
Destination Table - abc_summary(x, y) -> We will insert records one by one in this table

Calculation Stored Procedure - sp_test (@x, @x1 out, @x2 out) -> Takes value from Source table, does some processing and gets the summary and returns the 2 values as OUT Parameters [only out parameters can be used to get values frlom a stored procedure in a cursor loop, so you will have to modify a little part of your stored procedure as below]
-----------------------
CREATE PROCEDURE sp_test --This stored proc does some very heavy calculations!
@x int,
@x1 int out, --This is an out param for 1st value, to be added in your stored proc
@y1 int out --This is an out param for 2nd value, to be added in your stored proc
AS

--Instead of running SELECT x, y*100 FROM abc, we will return the 1 row resultset as out params in our stored proc.
SELECT @x1 = x, @y1 = y*100 FROM abc WHERE x = @x

--Alternatively you CAN+SHOULD perform the insert query here directly!! as:
--INSERT INTO abc_summary(x,y) VALUES(@x1, @y1)
GO
-----------------------






And here is the looping logic code that you can run and test in sql query analyzer:
------------------------------------
DECLARE @x int, @x1 int, @y1 int

--Declare the cursor
DECLARE test_cursor CURSOR FOR
SELECT x FROM abc

--Open the cursor
OPEN test_cursor

FETCH NEXT FROM test_cursor INTO @x
--Brings the cursor to the First Row of Table abc
--And fetches first record of abc.x FROM table abc into variable @x
Print '----------Begin: Loop Table abc ----------------'

WHILE @@FETCH_STATUS = 0
BEGIN

--Execute the stored proc with the input value of @x fetched from the cursor
EXEC sp_test @x, @x1 out, @y1 out
--The stored proc returns 2 output params @x1 and @y1

--Print the values
Print 'values = ' + CAST(@x1 AS VARCHAR) + ' - ' + CAST(@y1 AS VARCHAR)

--Do the Insert from these 2 variables to your destination table, say abc_summary:
INSERT INTO abc_summary(x,y) VALUES(@x1, @y1)
--Alternatively in the stored procedure sp_test itself, this query can be executed, to save execution time


FETCH NEXT FROM test_cursor INTO @x
--Brings the cursor to the Next Row of Table abc
--And fetches next record of abc.x FROM table abc into variable @x

END

--Free the resources
CLOSE test_cursor
DEALLOCATE test_cursor

--Finally, Show the resultset. If you want to... :)
SELECT * FROM abc_summary

-------------------------------------------------


rgds,
Ajit Anand
0
 
LVL 10

Assisted Solution

by:imrancs
imrancs earned 20 total points
ID: 12197457
I would do this like this,

Create Function dbo.GetSectionCount( @Customer_ID Int )
Returns Int
AS
Begin

      Declare @SecCount Int
      Set @SecCount = 0

      If Exists(Select * From Table1 Where Customer_Id = @Customer_Id)
            Set @SecCount = @SecCount + 1
      
      If Exists(Select * From Table2 Where Customer_Id = @Customer_Id)
            Set @SecCount = @SecCount + 1
      
      If Exists(Select * From Table3 Where Customer_Id = @Customer_Id)
            Set @SecCount = @SecCount + 1
      
      If Exists(Select * From Table4 Where Customer_Id = @Customer_Id)
            Set @SecCount = @SecCount + 1
      
      --repeate the above for each table


      Return @SecCount
End
      

Select Customer_ID, dbo.GetSectionCount(Customer_ID) SectionCount
From tbl_Customers
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 10

Expert Comment

by:imrancs
ID: 12197480
as ScottPletcher told that "Worst case you will have to use a cursor",
try to avoid use cursor untill unless there is must go situation.


Imran
0
 
LVL 5

Assisted Solution

by:ajitanand
ajitanand earned 460 total points
ID: 12197497
I suggested cursor as D-sect WANTED the looping logic explicitly.

Otherwise here is the fastest option I would suggest that gives the summary in 1 query
---------------------------------
SELECT count(c), id FROM
(
SELECT count(*) c, customerid id FROM tableA GROUP BY customerid
UNION
SELECT count(*) c, customerid id FROM tableB GROUP BY customerid
UNION
SELECT count(*) c, customerid id FROM tableC GROUP BY customerid
UNION
SELECT count(*) c, customerid id FROM tableD GROUP BY customerid
UNION
SELECT count(*) c, customerid id FROM tableE GROUP BY customerid
)
count_table INNER JOIN customers ON customers.id = count_table.id
---------------------------------


rgds,
Ajit Anand
0
 
LVL 4

Expert Comment

by:garima_sikarwar
ID: 12198248
declare @var1      int
declare @var2      int

declare c1 cursor for select col1,col3 from test1
open c1
      
      fetch next from c1 into @var1,@var2

while @@fetch_status=0
begin
      insert into test2(col1,col3) values (@var1,@var2)

      fetch next from c1 into @var1,@var2
end
close c1
deallocate c1

select * from test2
0
 

Author Comment

by:D-Sect
ID: 12199260
ajitanand has provided what looks like the ideal solution, so far -

Can I get some clarification really quick, though (In reference to his post):

1. I cannot simply insert the record(s) returned from a stored procedure into a table. I will need to insert the values of OUTPUT parameters into table (YES / NO).
2. When declaring the OUTPUT params in the SP, is OUT the exact same thing as OUTPUT? (YES / NO)

3. The following code will loop through all records in a table and PRINT the value of column "Customer_Id" from the table named "tblCustomers" (YES/NO):

DECLARE @x int
DECLARE test_cursor CURSOR FOR
SELECT Customer_Id FROM tblCustomers

OPEN test_cursor

FETCH NEXT FROM test_cursor INTO @x
-- [Question - This is the same as doing "@x = SELECT Customer_Id FROM tblCustomers"?]
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @x

FETCH NEXT FROM test_cursor INTO @x
END

CLOSE test_cursor
DEALLOCATE test_cursor

------------------------------------------------------------------------------------------

Guys - thanks again - I understand things much better now, but if I could get these questions clarified I would understand 100%.

And one final related question:

This statement:
DECLARE test_cursor CURSOR FOR
SELECT Customer_Id FROM tblCustomers

Declares a cursor. Is it possible to do:

DECLARE test_cursor CURSOR FOR
SELECT Customer_Id, Date_added FROM tblCustomers

and have both columns available to me in my loop - or do cursors only allow "one" column value to be FETCHED. I cannot FETCH 2 values from a cursor?

Again- THANKS for everyone's input. I needed 2 things: Loop a table, perform operations on each record & Insert vals from an SP into a table and I am very close and I do beleive that both have been answered, but would just like final clarification.

Thanks again, all!!

0
 
LVL 34

Assisted Solution

by:arbert
arbert earned 20 total points
ID: 12199565
"1. I cannot simply insert the record(s) returned from a stored procedure into a table. I will need to insert the values of OUTPUT parameters into table (YES / NO)."

Right, either that or you code a function.....

2. yes
3.  Yes, but why wouldn't you just use SELECT CUSTOMER_ID FROM tblCUSTOMERS?
0
 
LVL 34

Expert Comment

by:arbert
ID: 12199580
Of course, 2 should have been NO
0
 
LVL 34

Expert Comment

by:arbert
ID: 12199584
I meant 1 should have been NO--I just said right when I answered.
0
 

Author Comment

by:D-Sect
ID: 12199681
arbert - #3 was proof of concept to understand looping.

Thanks all! I have got it 100% thanks to all of you!!

I got stuck on the OUTPUT part of the SP, as I've worked with output params a lot, but never inside of an SP.

Let me sum this up for the next person who comes along:

#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

---------------------------------------------------------------------------------------------


0
 

Author Comment

by:D-Sect
ID: 12199766
Thanks again..

I've had to imagine there were better ways to do what I'm trying to do, and I'll move towards them very soon.

By understanding the concept of LOOPS in SPs and how to return the values of output parameters inside my T-SQL, I have learned some very valuable stuff - even if using these methods aren't the most elegant and efficient way to accomplish what I'm doing, it's something that I will not forget and may use later.

In the next revision of my stuff, I will strive for a more efficient solution using the information I'd gathered from the assisted answers in this question.

Great job to everyone!
0
 

Author Comment

by:D-Sect
ID: 12202947
I'm not sure if it's possible to ADD commments at this point but I have one more question:

What is THIS going to do:

SELECT count(c), id FROM
(
SELECT count(*) c, customerid id FROM tableA GROUP BY customerid
UNION
SELECT count(*) c, customerid id FROM tableB GROUP BY customerid
UNION
SELECT count(*) c, customerid id FROM tableC GROUP BY customerid
UNION
SELECT count(*) c, customerid id FROM tableD GROUP BY customerid
UNION
SELECT count(*) c, customerid id FROM tableE GROUP BY customerid
)
count_table INNER JOIN customers ON customers.id = count_table.id

If my table structure is this:

Customers table: Name tblCustomers , PK Customer_Id

and my other tables are named:

tbl1
tbl2
tbl3
 With the FK as Customer_Id

If it counts TABLES that have records for each record, this would be ideal.. I cannot get it to work though, using my real DB items, Only a couple parts work.

Thanks!
0
 
LVL 5

Expert Comment

by:ajitanand
ID: 12206065
This query will work perfectly in your case, if you want to add the no. of tables.
------------------------------------------------------------
SELECT tblCustomers.Customer_Id, Count(tblCount.Customer_Id)
FROM tblCustomers LEFT JOIN
(
SELECT DISTINCT Customer_Id FROM tbl1
UNION ALL
SELECT DISTINCT Customer_Id FROM tbl2
UNION ALL
SELECT DISTINCT Customer_Id FROM tbl3
) tblCount ON tblCustomers.Customer_Id=tblCount.Customer_Id

GROUP BY tblCustomers.Customer_Id
------------------------------------------------------------


rgds,
Ajit Anand
0
 

Author Comment

by:D-Sect
ID: 12209765
heh - Ajit - Awesome! That's it..

omg the loop thing I did took 8 seconds to run.. This is like 1 second for 16 tables..

Awesome!

Thanks for a great alternative to my solution and for ALSO helping me understand cursors and using the output params from inside another stored procedure!

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now