How to declare cursor in stored procedure? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to declare cursor in stored procedure?

Author

Topic

govindts
Starting Member

33 Posts

Posted - 2008-01-23 : 17:30:02

I am trying to decalare the cursor in the below stored procedure. Can any one please help me to correct the cursor declaration?? Basically, i am testing how to declare the cursor in stored procedure.

CREATE PROCEDURE STP_EMPSAL
@empno int,
@Employee_Cursor CURSOR VARYING OUTPUT
FOR SELECT empno FROM AdventureworksDW.dbo.emp
AS
OPEN Employee_Cursor;
FETCH NEXT FROM Employee_Cursor into @empno;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRAN
UPDATE emp set sal= sal+ 2000 where
empno = @empno and comm is null
mgr='Scott';
FETCH NEXT FROM Employee_Cursor into @empno;
COMMIT;
END;
CLOSE Employee_Cursor;
DEALLOCATE Employee_Cursor;

sshelper
Posting Yak Master

216 Posts

Posted - 2008-01-23 : 18:19:44

Looking at your code, you have to declare your CURSOR inside the SP and not as part of the parameters:

CREATE PROCEDURE STP_EMPSAL
@empno int
AS

DECLARE Employee_Cursor CURSOR
FOR SELECT empno FROM AdventureworksDW.dbo.emp
OPEN Employee_Cursor

SQL Server Helper
http://www.sql-server-helper.com

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-24 : 01:29:26

See if this works without a cursor

Select empno,sal+ 2000 as sal from emp where comm is null and mgr='Scott';

If it gives the result you want then

UPDATE emp set sal= sal+ 2000
where comm is null and mgr='Scott';

Madhivanan

Failing to plan is Planning to fail

govindts
Starting Member

33 Posts

Posted - 2008-01-24 : 09:21:59

Thanks sshelper. It helps.

Thanks madhivanan. You code will solve the problem. But i was trying to simulate the scenario for using the cursors in stored procedure. Anyway, Thanks again.

quote:
Originally posted by madhivanan

See if this works without a cursor

Select empno,sal+ 2000 as sal from emp where comm is null and mgr='Scott';

If it gives the result you want then

UPDATE emp set sal= sal+ 2000
where comm is null and mgr='Scott';

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-24 : 09:27:36

quote:
Originally posted by govindts

Thanks sshelper. It helps.

Thanks madhivanan. You code will solve the problem. But i was trying to simulate the scenario for using the cursors in stored procedure. Anyway, Thanks again.

quote:
Originally posted by madhivanan

See if this works without a cursor

Select empno,sal+ 2000 as sal from emp where comm is null and mgr='Scott';

If it gives the result you want then

UPDATE emp set sal= sal+ 2000
where comm is null and mgr='Scott';

Madhivanan

Failing to plan is Planning to fail

In this case you dont need a cursor

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources