We help IT Professionals succeed at work.

stored procedure help

emily_hall
emily_hall asked
on
Here is a snippet of my stored procedure:

/* First let's deal with the easiest table, the x table */
SELECT @numberOfRecords = Count(1) FROM x

/* Get the distinct sizes */
DECLARE Sizes CURSOR FOR SELECT DISTINCT Sizes FROM x

OPEN Sizes
         
/* Reference the named cursor with a cursor variable. */
DECLARE @currentSize CURSOR

SET @currentRecord = 0
WHILE(@currentRecord < @numberOfRecords )

BEGIN

SET @currentSize = Sizes

SELECT @selectedDescription = Description FROM zlk_Size WHERE MinimumSize < @currentSize AND MaximumSize > @currentSize                    

UPDATE x SET Description = @selectedDescription WHERE Size = @currentSize
         
DEALLOCATE @currentSize

FETCH NEXT FROM Sizes

@currentRecord = @currentRecord + 1

END



when i check this with query analyzer i get this error:

Server: Msg 16949, Level 16, State 1, Procedure sp_BreakIntoClasses, Line 26
The variable '@currentSize' is a cursor variable, but it is used in a place where a cursor variable is not valid.

it is complaining about the line with the update statement.  when i comment this line out though i get the same error on the same line!

what is going on here and how can i make this work?

thanks
emily
Comment
Watch Question

I suspect it is complaining about

SELECT @selectedDescription = Description FROM zlk_Size WHERE MinimumSize < @currentSize AND MaximumSize
> @currentSize                    

as the first error.

WHERE MinimumSize < [a cursor]
what does that mean?

Cursors are used by fetching fields of a row into variables then using those variables.
Also I don't think x will be updataeable (and doesn't contain Description).

Saying that it is a good idea not to learn how to use cursors as they are never necessary.

Now my little rant is over - back to the problem :-).
If you give a description of what you are trying to do maybe a better method can be suggested.
It looks like this can be done in a single update statement maybe.

something like
update x
set description = zlk_Size.Description
from zlk_Size
where x.size between zlk_Size.MinimumSize and zlk_Size.MaximumSize

If you want to do it in batches (i.e. the table is too big) you can use a temp table.

Oops x was the table not the cursor so ignore the updateable bit.

Actually I think this would be pretty close if

DECLARE @currentSize int (or whatever size is)

then fetch next from sizes into @currentSize

(still wouldn't use a cursor though).

If you look at cursor examples in bol you will see that they tell you when you get to the end of the cursor and you don't need the record count.
(still wouldn't use a cursor though).

Also @numberOfRecords is the number of records in x not in the cursor so you would be executing this too many times.

It's normal to do

SELECT @numberOfRecords = Count(*) FROM x
as most databases are optimised for this

(
as well as
if exists (select * from ...)
rather than
if exists ( select 1 from ...)

Think I'll go now before I annoy you too much.
(still wouldn't use a cursor though).
:-).

Author

Commented:
Right on - that update statement did the trick.  AND you'll be happy to note I didn't need the cursor anymore either!  :)

thanks!

:) emily

Author

Commented:
see previous comment.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.