I 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!
rbrryankbrown@.gmail.com wrote:
> I 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!
> rbr
>
Something like the code below will do what you need, but I'd encourage
you to think "outside the box" and come up with a way to do this without
a temp table. Temp tables are usually unnecessary results of trying to
do "procedural" processes in a set-based environment like SQL.
DECLARE @.LoopVar
SELECT @.LoopVar = 0
SELECT TOP 1 @.LoopVar = <someincrementalID>
FROM table
WHERE <someincrementalID> > @.LoopVar
WHILE @.@.ROWCOUNT > 0
BEGIN
EXEC storedproc @.LoopVar
SELECT TOP 1 @.LoopVar = <someincrementalID>
FROM table
WHERE <someincrementalID> > @.LoopVar
END|||>> I am trying to create a stored proc that will read in a list of values from one tab
le (possibly using a CTE?) then iterate through each record [sic] running another sp t
o populate a temp table and return all matching records [sic]. The logic would be
l
ike 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.
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.|||Trust the CELKO grasshopper... He is a curmudgeon and will not hesitate to
tell you what he thinks about a subject but search on his previous posts and
you will learn. Hang around here and lurk. Search is your friend... Good
luck.
"ryankbrown@.gmail.com" wrote:
> I 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!
> rbr
>|||I trust that you are all correct. As I stated, I am new to this from a
much more procedural background. As most of us do when faced with a new
problem we fall back on what we know best.
I already wrote a simple sp that does the job just fine. I was asked by
a superior to find a way to do it reusing another pre-existing sp to
acheive the same result. This is what spawned my question.
Thanks for imparting your wisdom upon me!
rbr
StvJston wrote:
> Trust the CELKO grasshopper... He is a curmudgeon and will not hesitate to
> tell you what he thinks about a subject but search on his previous posts a
nd
> you will learn. Hang around here and lurk. Search is your friend... Goo
d
> luck.
> "ryankbrown@.gmail.com" wrote:
>|||Oh, and by the way...
All is fine with my db. No redundancies here. The only complaint i've
heard from my programmers is that it may be over-normalized.
But thanks for the unproductive insult anyway.
rbr
rbr wrote:
> I trust that you are all correct. As I stated, I am new to this from a
> much more procedural background. As most of us do when faced with a new
> problem we fall back on what we know best.
> I already wrote a simple sp that does the job just fine. I was asked by
> a superior to find a way to do it reusing another pre-existing sp to
> acheive the same result. This is what spawned my question.
> Thanks for imparting your wisdom upon me!
> rbr
> StvJston wrote:
Sam_res03 wrote: