Iterating through a recordset

SQL Apprentice Question
am a newly indoctrinated SQL Server 2005 user. So, forgive me if this
question is amatuer.
I am trying to create a stored proc that will read in a list of values
from one table (Possibly using a cte?) then iterate through each record
running another sp to populate a temp table and return all matching
records. The logic would be like the following:

///////
create procedure spSample1


@column2 int


As


create table #tmptbl


(tmptblcol1 int not null)


with samplecte (var1 int)
As
(select column1 from table where column2 = @column2)
begin
while not end-of-file


set localvar1 = current value of samplecte var1


insert into #tmptbl exec spSample2 localvar1
end
////////


This is a very rough outline. Hopefully it helps with clarification of
what I am trying to acheive. Any help would be greatly appreciated.


Thank you in advance!



Celko Answers
>> I am trying to create a stored proc that will read in a list of values from one table (possibly using a CTE?) then iterate through each record [sic] running another sp to populate a temp table and return all matching records [sic]. The logic would be like the following: <<


You are still thinking of a file system - a magnetic tape file
system, to be more precise, doing a merge from a scratch tape.

Let's get back to the basics of an RDBMS. Rows are not records; fields
are not columns; tables are not files; there is no sequential access or
ordering in an RDBMS, so "first", "next" and "last" are totally
meaningless.


SQL is a declarative set-oriented language. We tend to write one
statement to do a job; if I want to insert data into a table, I write
something like:


INSERT INTO Foobar (a, b, c, ..)
SELECT x, y, z, ..
FROM Floob, Snarf, ..
WHERE ..;


One statement, no loops, no if-then control flow. You tell SQL
**what** you want, not **how** to do it. You want to write code as if
you were still in a 3GL procedural language. And I'll bet that your
schema is full of redundancies, too.



>> This is a very rough outline. Hopefully it helps with clarification of what I am trying to achieve. <<


Nope, it was too vague to be usable for any concrete suggestions. But
it did demonstrate that you missed the very foundations of RDBMS. Get
a few books (insert shameless plug for my stuff here), clear out
everything you already know and start over. "To drink new tea, you
must first empty the old tea from your cup" - Zen proverb.