Iterating through a recordset

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:

Issue while using Transfer Sql Server Objects tasks

Hi
I am trying to use Transfer SQL Server Object task to transfer various
DB objects from a Source DB to a newly created Destination DB. The
Destination DB is blank before this attempted transfer.
Now our source Db contains various SPs and views which internally
reference other SPs/Views of the same DB (source DB). For example we
have 2 SPs - SP1 and SP2. Now SP1 internally executes SP2. Whie using
transfer objects task, if we try to transfer all SPs only, we get an
error saying that SP2 does not exist while creating SP1. It seems SSIS
compiles objects while transfering them. We tried to set the
DelayValidation property of the entire package as well as the task to
True, but this didnt help either.
It would be great if someone could suggest some mechanism to prevent
compilation of objects, or any other method using which we can avoid
the problem that we are facing.
Thanks
Regards,
Piyush
I would redirect any failure to a logging table or other, or even ignore
errors.
On 1st pass sp1 will fail, but sp2 should still be created, correct? Then
perform a second pass which will cause sp2 to fail because already exists,
however sp1 will compile and create fine since dependency now exists...
You will get a quicker response if you post to dts group for ssis questions.
good luck.
ChrisB MCDBA
MSSQLConsulting.com
"whirlwnd" wrote:

> Hi
> I am trying to use Transfer SQL Server Object task to transfer various
> DB objects from a Source DB to a newly created Destination DB. The
> Destination DB is blank before this attempted transfer.
> Now our source Db contains various SPs and views which internally
> reference other SPs/Views of the same DB (source DB). For example we
> have 2 SPs - SP1 and SP2. Now SP1 internally executes SP2. Whie using
> transfer objects task, if we try to transfer all SPs only, we get an
> error saying that SP2 does not exist while creating SP1. It seems SSIS
> compiles objects while transfering them. We tried to set the
> DelayValidation property of the entire package as well as the task to
> True, but this didnt help either.
> It would be great if someone could suggest some mechanism to prevent
> compilation of objects, or any other method using which we can avoid
> the problem that we are facing.
> Thanks
> Regards,
> Piyush
>