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

#2: sp_MSforeachtable

Example:

sp_MSforeachtable 'PRINT ''?'''

will execute "PRINT <tableName>" for each table

>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 ''?'''

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

You did want to loop through records right?  spMSForeachtable will loop through tables.

Worst case you will have to use a cursor.

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.

>spMSForeachtable will loop through tables.
Duh. I'll go back to bed now...

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.

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

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

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

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!!

Of course, 2 should have been NO

I meant 1 should have been NO--I just said right when I answered.

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

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

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!

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!

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

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!