sql server 2012 - avoinding cursor

Posted on 2012-09-20
Last Modified: 2012-10-05
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
Question by:enrique_aeo
    LVL 37

    Expert Comment

    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
    LVL 30

    Assisted Solution

    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.
    LVL 12

    Assisted Solution

    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.
    LVL 30

    Accepted Solution

    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 )
    select * from A;

    Open in new window

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

    Using cursor:
    declare @a nvarchar(25),
            @x nvarchar(25)
    FOR SELECT a,x FROM hnasr01.dbo.A
    OPEN MyCursor
    FETCH NEXT FROM MyCursor INTO @a, @x
    	if cast(@a as int) % 2=0 
    		UPDATE  dbo.A set x='I am an even number' WHERE CURRENT OF MyCursor;
    		UPDATE  dbo.A set x='I am an odd number' WHERE CURRENT OF MyCursor;
    	FETCH NEXT FROM MyCursor INTO @a,@x
    SELECT a, x FROM A;
    close MyCursor

    Open in new window

    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!

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    Join & Write a Comment

    In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now