September 10, 2003 at 5:42 pm
I've developed a procedure the compiles and yet doesn't set the local variables when reading through the cursor. @LruListOut and @CurOut both return (null) to the query analyzer. I've used RAISERROR and PRINT to output temporary results to the query analyzer output pane. Results show the successful cursor has four records selected. I've used a working procedure as the template for creating and working the cursor. Any suggestions would be greatly appreciated. I seem to be overlooking something obvious.
BEGIN
DECLARE crs_lru CURSOR
FOR SELECT DISTINCT PN, Cage, Description FROM tblPN WHERE PN + Cage IN (SELECT DISTINCT PN + Cage
FROM dbo.tblToRef
WHERE (EndItem IN
(SELECT EndItem
FROM dbo.tblToRef
WHERE PN = @PN AND Cage = @Cage)) AND (EndItem = ID))
OPEN crs_lru
DECLARE @PnItem nvarchar(32), @CageItem nvarchar(7), @NameItem nvarchar(40), @LruListOut nvarchar(255)
DECLARE @CurCount int, @CurOut nvarchar(10)
IF @@CURSOR_ROWS = 0
BEGIN
RAISERROR ('Unsuccessful cursor %d records.', 16, 1, @@CURSOR_ROWS)
RETURN
END
ELSE
RAISERROR ('Successful cursor %d records.', 16, 1, @@CURSOR_ROWS)
WHILE @@FETCH_STATUS <> -1
BEGIN
FETCH NEXT FROM crs_lru INTO @PnItem, @CageItem, @NameItem
SET @CurCount = @CurCount + 1
SET @LruListOut = @LruListOut + @PnItem + ', ' + @CageItem + ', ' + @NameItem + CHAR(13)
RAISERROR('ListItem %s', 16, 1, @LruListOut)
END
CLOSE crs_lru
DEALLOCATE crs_lru
SET @CurOut = CAST(@CurCount As nvarchar(10))
RAISERROR ('%s cursor count %s', 16, 1, @LruListOut, @CurOut)
RETURN
END
September 10, 2003 at 5:55 pm
You're declaring but not initialising @LruListOut and @CurOut. They are NULL after declaring them, and any subsequent operations where they are one of the operands will also return NULL.
SET @CurOut = 0
SET @LruListOut = ''
Cheers,
- Mark
Cheers,
- Mark
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply