how to make a curosr - SQL Server Forums

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 make a curosr

Author

Topic

immad
Posting Yak Master

230 Posts

Posted - 2014-01-07 : 00:41:14

Hello,
please can any one provide me a very simple example that how to make a
cursor in Sql server 2008 R2 and why we need cursor in sql server 2008 R2.
thanks for the help.

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-07 : 00:52:01

Ideally you would need cursor only in cases where you want to go for row by row processing ie like case where you want to execute procedure for each row in a resultset. Other than few scenarios like this all other cases can be best handled by means of set based code.

http://blog.sqlauthority.com/2007/01/01/sql-server-simple-example-of-cursor/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

immad
Posting Yak Master

230 Posts

Posted - 2014-01-07 : 01:09:59

quote:
Originally posted by visakh16

Ideally you would need cursor only in cases where you want to go for row by row processing ie like case where you want to execute procedure for each row in a resultset. Other than few scenarios like this all other cases can be best handled by means of set based code.

http://blog.sqlauthority.com/2007/01/01/sql-server-simple-example-of-cursor/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

page is not opening
well this is a example i made for cursor

i write this

CREATE PROCEDURE Usp_cursor_test
AS
BEGIN
DECLARE @ID INT
DECLARE @Company NVARCHAR(50)

DECLARE cur_print CURSOR FOR
SELECT ID,
Company
FROM Comp

OPEN cur_print

FETCH NEXT FROM cur_print INTO @ID, @Company

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @ID
PRINT @Company

FETCH NEXT FROM cur_print INTO @ID, @Company
END

CLOSE cur_print

DEALLOCATE cur_print
END

and result show this message
Command(s) completed successfully
now how i run this cursor. is it run automatically ?

immad uddin ahmed

waterduck
Aged Yak Warrior

982 Posts

Posted - 2014-01-07 : 02:14:05

Hi, i think you had just created the stored procedure but you haven execute it?

Grifter
Constraint Violating Yak Guru

274 Posts

Posted - 2014-01-07 : 07:21:58

quote:
Originally posted by immad

quote:
Originally posted by visakh16

Ideally you would need cursor only in cases where you want to go for row by row processing ie like case where you want to execute procedure for each row in a resultset. Other than few scenarios like this all other cases can be best handled by means of set based code.

http://blog.sqlauthority.com/2007/01/01/sql-server-simple-example-of-cursor/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

page is not opening
well this is a example i made for cursor

i write this

CREATE PROCEDURE Usp_cursor_test
AS
BEGIN
DECLARE @ID INT
DECLARE @Company NVARCHAR(50)

DECLARE cur_print CURSOR FOR
SELECT ID,
Company
FROM Comp

OPEN cur_print

FETCH NEXT FROM cur_print INTO @ID, @Company

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @ID
PRINT @Company

FETCH NEXT FROM cur_print INTO @ID, @Company
END

CLOSE cur_print

DEALLOCATE cur_print
END

and result show this message
Command(s) completed successfully
now how i run this cursor. is it run automatically ?

immad uddin ahmed

You must only be executing the procedure, this will not show results only that it processed correctly. Also after the first execute, which creates the procedure, you need to change this line:

CREATE PROCEDURE Usp_cursor_test

To:

ALTER PROCEDURE Usp_cursor_test

Or it will fail to process because the code will be trying to recreate the procedure you already have created. This is if you are making changes and re-executing the code to check it has no errors.

To run the procedure just highlight the name [Usp_cursor_test] and press F5 or Run, this should give you the results output.

G

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2014-01-07 : 07:45:37

What are you trying to do? Note that in many cases you don't need a cursor

Madhivanan

Failing to plan is Planning to fail

immad
Posting Yak Master

230 Posts

Posted - 2014-01-08 : 05:02:14

quote:
Originally posted by madhivanan

What are you trying to do? Note that in many cases you don't need a cursor

Madhivanan

Failing to plan is Planning to fail

i just want to learn cursor.when i execute proceudre
its give me this result

(1 row(s) affected)
9
3
3
1
4
4

and its insert in city table only one row and its insert in cityid=9

this is the proceudre

ALTER PROCEDURE [dbo].[Usp_cursor_test]
AS
BEGIN

DECLARE cur_print CURSOR FOR
SELECT ID
FROM Comp

OPEN cur_print
FETCH NEXT FROM cur_print INTO @ID
INSERT INTO city (City_ID) values (@ID);

WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @ID

FETCH NEXT FROM cur_print INTO @ID
END

CLOSE cur_print
DEALLOCATE cur_print
END

i want to insert whole ids

immad uddin ahmed

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-08 : 05:32:11

thats because insert code is outside the loop. if you want to insert all bring it inside loop like below


ALTER PROCEDURE [dbo].[Usp_cursor_test]
AS
BEGIN

DECLARE cur_print CURSOR FOR
SELECT ID
FROM Comp

OPEN cur_print
FETCH NEXT FROM cur_print INTO @ID
WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO city (City_ID) values (@ID);



PRINT @ID

FETCH NEXT FROM cur_print INTO @ID
END

CLOSE cur_print
DEALLOCATE cur_print
END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources