CASE Statement with variables 2

[Robinstwitcher]

I have the following code which returns "Incorrect syntax near the keyword 'THEN'." Please advise me on my errors!

declare @ImportTblAP nvarchar (2),
@ImportTblFY nvarchar (2),
@FYSTRING nvarchar (10),
@ModAPFY nvarchar (12)

select @ImportTblFY = rtrim(max(FY)),@ImportTblAP = rtrim(Max(AP))
from DBATblTempImport

SELECT @ImportTblFY =
CASE
WHEN '07' THEN @FYSTRING = '2006/07 AP'
WHEN '08' THEN @FYSTRING = '2007/08 AP'
WHEN '09' THEN @FYSTRING = '2008/09 AP'
WHEN '10' THEN @FYSTRING = '2009/10 AP'
ELSE 'Unknown'
END,


select @MODAPFY=@FYSTRING+@ImportTblAP

BEGIN

print @modapfy

END

 

[AlexCuse]

You need to specify what your case is testing for. You want to set @FYSTRING = '2006/07 AP' when SOMETHING = '07'. So try

CASE [b][COLOR=red]SOMETHING[/color][/b]
        WHEN '07' THEN @FYSTRING = '2006/07 AP'
        WHEN '08' THEN @FYSTRING = '2007/08 AP'
        WHEN '09' THEN @FYSTRING = '2008/09 AP'
        WHEN '10' THEN @FYSTRING = '2009/10 AP'
        ELSE 'Unknown'
    END,

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[bborissov]

SELECT @FYSTRING = CASE WHEN @ImportTblFY = '07'
                            THEN '2006/07 AP'
                        WHEN @ImportTblFY = '08'
                             THEN '2007/08 AP'
                        WHEN @ImportTblFY = '09'
                             THEN '2008/09 AP'
                        WHEN @ImportTblFY = '10'
                             THEN '2009/10 AP'
                    ELSE 'Unknown' END,

or

SELECT @FYSTRING =  CASE @ImportTblFY
                         WHEN '07'
                              THEN '2006/07 AP'
                         WHEN '08'
                              THEN '2007/08 AP'
                         WHEN '09'
                              THEN '2008/09 AP'
                         WHEN '10'
                              THEN '2009/10 AP'
        ELSE 'Unknown' END,

(not tested)

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP