Clicky

I am super rusty  and would appreciate any help with understanding this code

What are the cursor, fetch, and   CURSOR LOCAL STATIC "functions" and what is an example of the logic that will go in to this …  /* Your process logic here */ section





DECLARE CUR_ITEMWHSE CURSOR LOCAL STATIC FOR
    SELECT
        item,
        whse,
        qty_on_hand,
        qty_alloc_co
    From itemwhse
    where itemwhse.qty_on_hand > 0 and qty_alloc_co > 0

OPEN CUR_ITEMWHSE
    WHILE @Severity = 0
    BEGIN
        FETCH CUR_ITEMWHSE INTO
            @item,
            @whse,
            @qty_on_hand,
            @qty_alloc_co

        IF @@FETCH_STATUS = -1
            BREAK

        …  /* Your process logic here */

     END

CLOSE CURSOR CUR_ITEMWHSE

asked 12/11/2011 03:50

December2000's gravatar image

December2000 ♦♦


8 Answers:
A cursor is essentially a loop. The SQL above OPEN CUR_ITEMWHSE is the recordset that is being looped through.

>what is an example of the logic that will go in to this …  /* Your process logic here */ section
Whatever you want to process for each record in the recordset.
link

answered

jimhorn's gravatar image

jimhorn

Thank you for your prompt response  Jim,

 >Whatever you want to process for each record in the recordset

 Would that be like a select statement?, if not a select statement can you show me an example?

 What is  @Severity = 0 ?
link

answered 2011-12-11 at 11:58:49

December2000's gravatar image

December2000

Usually if you have a cursor then you're doing something to the indidivual record that you coulnd't pull off in a single SQL statement for the entire recordset, such as complex logic, complex sorting order, multiple updates, etc.

>if not a select statement can you show me an example?
It all depends on what you're trying to accomplish, so I have no way to answer that question.
link

answered 2011-12-11 at 12:04:10

jimhorn's gravatar image

jimhorn

Would that be used if you want to use abs()?
link

answered 2011-12-11 at 12:25:52

December2000's gravatar image

December2000

Probably not, unless they're some kind of funky logic to what you're abs()-ing
link

answered 2011-12-11 at 12:29:06

jimhorn's gravatar image

jimhorn

what is  @Severity = 0 ?
link

answered 2011-12-11 at 12:37:34

December2000's gravatar image

December2000

To combine the responses of jimhorn a cursor is a loop that goes an item at a time through the data returned for the select.
In your case, the data in the database is not altered.
 
You can define a cursor that has the option to modify some or all columns in the records return by including a for update and defining the columns that can be updated.
http://msdn.microsoft.com/en-us/library/aa258831%28v=sql.80%29.aspx

@severity is a variable that is set by the system
http://www.tek-tips.com/faqs.cfm?fid=6652
Which either is loaded earlier in the code. or it can be a  variable defined for other purposes.

local scope and static means that the data obtained in the select will not change should changes in the database be made.
http://msdn.microsoft.com/en-us/library/ms180169.aspx
link

answered 2011-12-11 at 12:45:24

arnold's gravatar image

arnold

Thanks for the split.  Good luck with your project.  -Jim
link

answered 2011-12-11 at 17:32:13

jimhorn's gravatar image

jimhorn

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

×4
×20
×6

Asked: 12/11/2011 03:50

Seen: 324 times

Last updated: 12/11/2011 10:39