SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to make a curosr
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 01/07/2014 :  00:41:14  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 01/07/2014 :  00:52:01  Show Profile  Reply with Quote
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
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 01/07/2014 :  01:09:59  Show Profile  Reply with Quote
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
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
961 Posts

Posted - 01/07/2014 :  02:14:05  Show Profile  Reply with Quote
Hi, i think you had just created the stored procedure but you haven execute it?
Go to Top of Page

Grifter
Constraint Violating Yak Guru

263 Posts

Posted - 01/07/2014 :  07:21:58  Show Profile  Reply with Quote
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

Edited by - Grifter on 01/07/2014 07:25:50
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 01/07/2014 :  07:45:37  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
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
Go to Top of Page

immad
Posting Yak Master

Pakistan
219 Posts

Posted - 01/08/2014 :  05:02:14  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/08/2014 :  05:32:11  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000