Create and iterate through a recordset in a Stored Procedure

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

Other Forums

Other Topics

Create and iterate through a recordset in a Stored Procedure

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-08-25 : 19:03:32

Ivan writes "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"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2004-09-01 : 00:37:30

Hi Ivan

[First Method]
I may be missing something, but it it looks to me like you've missed out actually storing the imported_file_id value anywhere.

I suggest you add

declare @imported_file_id integer

select top 1 @imported_file_id = imported_file_id from imported_positions where strategy = @strategy and
datediff(day,date_created,current date) = 0 group by imported_file_id

after your while statement -

Also - how does the number of records in imported_positions reduce? Maybe you want to add

and imported_file_id < @imported_file_id

to the end of the where clause in your while statement, so that it doesn't loop forever.

Finally, I'm not really sure you actually want to group by in your while statement - I think you actually just want to order by.

[Second Method]

Similar to the first method comments - try something like the following


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
--
--not sure if sybase supports top 1
select top 1 @id = imported_file_id from #mytemp order by imported_file_id
-- don't think you need less than
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

delete from #mytemp where imported_file_id = @id
select top 1 @id = imported_file_id from #mytemp order by imported_file_id
end
set rowcount 0
commit transaction
end

Also, if you're having problems with setting rowcount 1/0 etc, you could replace

while @@rowcount <> 0

with

while exists (select * from #mytemp)

=======

hope some of that helps. Post back if you still have problems.

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources