[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 371
  • Last Modified:

sql server 2012 - avoinding cursor

Hi experts
i am reading about cursor

Alternatives to cursors: Windowing functions and Aggregate functions

Can you explaine me with transact sql code about Alternatives to cursors
0
enrique_aeo
Asked:
enrique_aeo
  • 2
3 Solutions
 
momi_sabagCommented:
it depends on your exact need, but if for example  you want to have a cursor so that you would be able to access the current row and have the previous row values stored in variables, windowing functions let you do that without a cursor
0
 
hnasrCommented:
If you know VBA for Access, cursor is similar to a recordset.
It allows you to loop through records and check values and update relevant fields.

You can issue update statement to do the job for few qualified records at once.

Feedback for more comments! If you can think of an example.
0
 
Jared_SCommented:
I agree that this absolutely depends on your need. You won't find a single correct method for replacing a cursor.

The first step is to think in terms of what you're going to do with the column verses what you're going to do with the row.

I've replaced some old cursor queries making use of tally tables, temp tables, and getting creative with my joins - and I know there are LOTS of methods that I haven't used or don't know yet.

This type of work gives you an opportunity to think creatively and apply your knowledge. It can be fun, and sometimes frustrating.
0
 
hnasrCommented:
Here is an example:
Problem: Table A (a nvarchar(25), x nvarchar(25)
a  x
1  Null
2  Null

Required Output:
a  x
1  I am an odd number
2  I am an even number

Using a Query:
UPDATE A SET x =
(select 	case 
			when Cast(a as int) % 2=0 THEN ('I am an even number')
			else ('I am an odd number' )
			end )
go
select * from A;

Open in new window

Result:
a      x
1      I am an odd number
2      I am an even number

Using cursor:
declare @a nvarchar(25),
        @x nvarchar(25)

DECLARE MyCursor CURSOR 
FOR SELECT a,x FROM hnasr01.dbo.A
FOR UPDATE OF x;

OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @a, @x

WHILE @@FETCH_STATUS=0
BEGIN
	if cast(@a as int) % 2=0 
		UPDATE  dbo.A set x='I am an even number' WHERE CURRENT OF MyCursor;
	else
		UPDATE  dbo.A set x='I am an odd number' WHERE CURRENT OF MyCursor;
	FETCH NEXT FROM MyCursor INTO @a,@x
END
SELECT a, x FROM A;
close MyCursor
DEALLOCATE MyCursor
go

Open in new window

Result:
a      x
1      I am an odd number
2      I am an even number

Cursor allows you to do more checks and processes.

Hope that gives you a start!
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now