doubt on stored procedure

[viji]

hi friends,


while exists(select neospc_id from config_ghostsets
where setname= @sname)
begin
select @pc=neospc_id from config_ghostsets where
setname= @sname

if(@option = 1)
insert into pms_reboot_list(pc) values(@pc)

end

in this sp
select neospc_id from config_ghostsets where setname=
@sname
for this query i want to retrieve all the rows from
the data base
how to retrieve using loop in this sp itself...

in that @pc i want to retrieve all records... from
db.h can i do this

thanks,
viji.





__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com

[Thread]

Hi Viji,

I think you have to use cursors for this. Lets wait for other
suggestions.

Regards
Thread

[BabuLives]

Ya.. u can use cursor for that...
it goes like...

alter proc sppro
as
DECLARE @pc char(11)

Declare cur cursor for select * from t1 --put ur select query here

OPEN cur

FETCH NEXT FROM cur INTO @pc

WHILE @@FETCH_STATUS = 0
BEGIN

--put ur if loop logic here to insert
insert into t2 values(@pc)

FETCH NEXT FROM cur INTO @pc
END
CLOSE cur
DEALLOCATE cur
Go

i think am correct...

Is there any other simple way to do this????

Regards,
Satheesh

[Raja Prakash]

Hey guys.. i hope this is the only way to do loop and simplest one... and you should use it depending upon how much data you are looping because i consumes a lot of resource....

 

Thank you

Raja Prakash

 

[Thread]

Yes this is the only way cos the problem here is we have to get all the
col values. and doing select @s=name from table will give only the last
value. Cursor is the only way to get all the values. Here the scenario
is simliar to an array..

Regards
Thread

[Siva CH]

Hi Vijii,

Using cursors is one option. The other option is to store the result of the query

 

select neospc_id from config_ghostsets where
setname= @sname
 

 in a temporary table/table variable and loop through it using WHILE, so that you can avoid cursors and gain good performance.

 

I hope this helps

 

Regards,

Siva

 

[Ravi]

yes i agree with him..........

 

regards,

ravi

[BabuLives]

So this procedure is have greater performance than using cursor???

alter proc spprotemptable
as

Select col into temp# from t1 --put ur select query here

insert into t2 select * from temp# --put ur if loop logic here to
insert

drop table temp#

GO

Regards,
Satheesh