Case in Fetch..? 1

[kenjoswe]

Hi all,

Why can't I use the CASE statement together with FETCH

/Kent J.

DECLARE @type char(3),@cnt varchar(3),@test varchar(3)

DECLARE type_cursor CURSOR FOR
SELECT cnt,Type=left(tti,3) FROM tti_test

OPEN type_cursor

FETCH NEXT FROM type_cursor
INTO @cnt,@type

WHILE @@FETCH_STATUS = 0
BEGIN

case
when @type='315' then @test=@type
when @type='316' then @test=@test
end
PRINT @Cnt + ' ' + @type + ' ' + @test

FETCH NEXT FROM type_cursor
INTO @cnt,@type
END

CLOSE type_cursor
DEALLOCATE type_cursor

 

[JamesLean]

You can't use CASE like that. Use IF:

IF @type = '315'
  SET @test = @type
ELSE
  SET @test = @test

BTW, the use of a cursor here seems unnecessary - try and avoid them if possible.

--James