Create and iterate through a recordset in a StProc

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Create and iterate through a recordset in a StProc

Author

Topic

ivanivanivan
Starting Member

2 Posts

Posted - 2004-07-23 : 07:50:29

Hi there,

I am writing a stored procedure in Sybase:

I have been trying for ages to return a recordset of ID's from a table by a GROUP SQL statement. Then looping through another table and finding those ID's from the recordset. And as I find each one of those ID, update that same table with the values but changing 2 field values. I hope that makes sense.

I have tried the following 2 bits of code below but to no avail: Can someone help me please?

FIRST METHOD
===========

ALTER procedure DBA.sp_firstone(@strategy varchar(255))
/* ( @parameter_name datatype [= default] [output], ... ) */
as
begin
begin transaction
--
declare @fileName varchar(255)
declare @tradeNumber integer
declare @id integer
--
-- update the imported files table with deleted record
while exists(select imported_file_id from imported_positions where strategy = @strategy and
datediff(day,date_created,current date) = 0 group by imported_file_id)
--
set rowcount 1
select @id=(select id from imported_position_files where id = imported_file_id)
select @fileName=(select file_name from imported_position_files where id = @id)
select @tradeNumber=(select trade_number from imported_position_files where id = @id)
set rowcount 0

insert into imported_position_files(file_name,date_time,successful,trade_number) values(@fileName,current date,'D',@tradeNumber)
--
commit transaction

SECOND METHOD
=============

ALTER procedure DBA.sp_secondone(@strategy varchar(255))
/* ( @parameter_name datatype [= default] [output], ... ) */
as
begin
begin transaction
--
--
declare @id integer
declare @fileName varchar(255)
declare @tradeNumber integer
--
set rowcount 0
select* into #mytemp from imported_positions
set rowcount 1
--
select @id = imported_file_id from #mytemp
while @@rowcount <> 0
begin
set rowcount 0
select* from #mytemp where imported_file_id = @id
select @fileName=(select file_name from imported_position_files where id = @id)
select @tradeNumber=(select trade_number from imported_position_files where id = @id)
insert into imported_position_files(file_name,date_time,successful,trade_number) values(
@fileName,current date,'D',@tradeNumber)
set rowcount 1
select @id = imported_file_id from #mytemp
end
set rowcount 0
commit transaction
end

Thanks in advance!!!!!!!!!!!!!
Ivan

rihardh
Constraint Violating Yak Guru

307 Posts

Posted - 2004-07-23 : 08:07:22

Did you say Sybase?

ivanivanivan
Starting Member

2 Posts

Posted - 2004-07-23 : 08:09:50

quote:
Originally posted by rihardh

Did you say Sybase?

yes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources