emily_hall
asked on
stored procedure help
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
/* 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
thanks!
:) emily
ASKER
see previous comment.
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).
:-).