Cannot figure out the error in the stored procedur

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 2005 Forums

Transact-SQL (2005)

Cannot figure out the error in the stored procedur

Author

Topic

gamaz
Posting Yak Master

104 Posts

Posted - 2009-04-16 : 17:19:42

Hi,
I am trying to write a stored procedure that takes the data from cursor processing that stored it in a temporary table. However I am getting an error while compiliing this stored procedure. I appreciate any help for resolution of this problem.
CODE:
ALTER procedure [dbo].[test_release2]

AS
set nocount on
--Create a temporary table which will stored the result processed in cursor
create table #tblhrstorun
(
ID int IDENTITY,
mach varchar(50),
ordnum varchar(10),
hrstorun1 int
)

--Declare the cursor
declare hrstorun_round cursor
for
select mach, ordnum, round(hrstorun, 0) as hrstorun1
from tblprojectionold1
order by mach

--Declare the variables that will hold data for each row

Declare @ordnum varchar(10),
@mach varchar(50),
@hrstorun float,
@hrstorun1 int,
@msgstr varchar(200)

--Print the heading
PRINT 'MachineNo--------------OrdNum-------------HrsToRun'
--Open cursor and fetch the rows
open hrstorun_round
fetch next from hrstorun_round into @mach, @ordnum, @hrstorun1

while @@fetch_status = 0

BEGIN

--Insert the row fetched by cursor to the temporary table
insert into #tblhrstorun(mach,ordnum,hrstorun1)
(@mach, @ordnum, @hrstorun1) ****** THIS LINE IS THROWING THE ERROR MESSAGE

fetch next from hrstorun_round into @mach, @ordnum, @hrstorun1
END
select * from #tblhrstorun

close hrstorun_round
deallocate hrstorun_round

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2009-04-16 : 17:24:26

--Insert the row fetched by cursor to the temporary table
insert into #tblhrstorun(mach,ordnum,hrstorun1)
VALUES(@mach, @ordnum, @hrstorun1)

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

gamaz
Posting Yak Master

104 Posts

Posted - 2009-04-16 : 17:57:48

Thanks yosiasz for the help. I appreciate it.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-16 : 18:44:14

Is this just an exercise to use a cursor? If not, you would be better off using a set-based solution.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources