Regarding cursor...pls help me...

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

General SQL Server Forums

New to SQL Server Programming

Regarding cursor...pls help me...

Author

Topic

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2005-11-09 : 01:47:59

Hello friends!!
I want how cursors are working....here
I wrote one store proc...which received parameter ID ..it check for the existing records if it there then it only update it with
and if it found new then it insert in....
Perticularly i am using cursor because i want how cursor are working but when i ran this it goes for indefinately long time....i donot know why...can any body help me where i went wrong here....

CREATE PROC TEST_E
@ID NUMERIC
AS

DECLARE @NAME AS VARCHAR(4)
SET @NAME = 'NNN'
DECLARE EMT_TE CURSOR FOR SELECT * FROM EMP
OPEN EMT_TE
FETCH NEXT FROM EMT_TE INTO @ID,@NAME
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE EMP
SET NAME = @NAME
WHERE ID = @ID

PRINT @ID
PRINT @NAME
IF @@ROWCOUNT = 0
BEGIN
INSERT INTO EMP VALUES (@ID,@NAME)
END
FETCH NEXT FROM EMT_TE INTO @ID,@NAME

END

CLOSE EMT_TE
DEALLOCATE EMT_TE

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-09 : 01:55:28

Well its always good to avoid cursors..

I guess for what you are doing you can do somthing like this ..
-- This update the name as you wanted ..
Update Emp Set [Name] = Emp1.Name From Employee Emp1 Inner Join Emp On
Emp1.Id = Emp.ID
--
Insert Emp
Select [ID],[Name] From Employee Where ID Not In (Select EmpID From Employee)

???

Hope this helps.

Complicated things can be done by simple thinking

under2811
Constraint Violating Yak Guru

366 Posts

Posted - 2005-11-09 : 02:04:22

Hello Chi...

I did such query before by using Simple IF/ELSE statement and i got result also but here perticularly i am using cursor because i want to know how it is working here....and this cursor seems to be ok then why it took so long time to execute.....hardly i have 10 records in my Emp table......

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-09 : 02:34:36

oh.. oks..

well. i dont know whether i m right or wrong..

what i feel is that..

Just print what is the @@RowCount comming out to be..

Since i feel that becuase @@RowCount is comming to be 0 its keeps on adding the new record in the emp table.. and due to which the number of itrations are increasing... ????

And Check like this .. which may give you the better idea..

Select * From Emp
-- Run the proc..
Select * From Emp

Just check the difference between the 2 tables.. and number of new records inserted??
This is just the guess..

Complicated things can be done by simple thinking

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-09 : 03:17:23

Set based approach is much more easier than using Cursor.

Refer this to know how you can avoid cursors
http://www.sql-server-performance.com/cursors.asp

Madhivanan

Failing to plan is Planning to fail

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2005-11-09 : 14:18:36

First the cursor should specify the columns and not use *
Second the cursor should use the @id otherwise you are updating every record and do not need the IF
Third The if needs to be outside of the WHILE since if the id does not exist yuou will never get into the while loop
That said if you just want to know how to use a cursor. Here is a working example but I would recommend that you use madhivanan's approach

CREATE PROC TEST_E
@ID NUMERIC,
@Name Varchar(4)='NOID'
AS
BEGIN
DECLARE @Check as int
SET @NAME = 'NNN'

DECLARE EMT_TE CURSOR FOR
SELECT [id], [name] FROM EMP WHERE id = @ID
OPEN EMT_TE
FETCH NEXT FROM EMT_TE INTO @ID,@NAME
set @check = 0

WHILE @@FETCH_STATUS = 0
BEGIN

UPDATE EMP SET NAME = @NAME WHERE ID = @ID
set @Check = @@ROWCOUNT

PRINT @ID
PRINT @NAME
print '@Check = ' + Cast(@Check as varchar)

FETCH NEXT FROM EMT_TE INTO @ID,@NAME

END

-- If the id does not exist you will never enter the while loop so the if needs to be outside of it
IF @Check = 0
BEGIN
print 'Nothing updated for ' + cast(@ID as varchar) + ' ' + @NAME + ' INSERTING data'
INSERT INTO EMP VALUES (@ID,@NAME)
END

CLOSE EMT_TE
DEALLOCATE EMT_TE
END
go

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources