CODEWITHSUNDEEP
sql-serversql-server-2008
user300485
user300485

Reputation: 525

IF condition in SQL Server stored procedure

Not sure what I am doing wrong here. Could you please correct me?

I am writing this logic in my stored procedure but I get an error:

DECLARE @PERIOD AS VARCHAR(7)

BEGIN
    IF(MONTH(GETDATE()) = 1 or MONTH(GETDATE()) = 2 or MONTH(GETDATE()) = 3)
    BEGIN
        SET @PERIOD = YEAR(GETDATE() + ' Q1'
    END
    ELSE IF(MONTH(GETDATE()) = 4 or MONTH(GETDATE()) = 5 or MONTH(GETDATE()) = 6)
    BEGIN
        SET @PERIOD = YEAR(GETDATE() + ' Q2'
    END
    ELSE IF(MONTH(GETDATE()) = 7 or MONTH(GETDATE()) = 8 or MONTH(GETDATE()) = 9)
    BEGIN
        SET @PERIOD = YEAR(GETDATE() + ' Q3'
    END
    ELSE IF (MONTH(GETDATE()) = 10 or MONTH(GETDATE()) = 11 or MONTH(GETDATE()) = 12)
    BEGIN
        SET @PERIOD = YEAR(GETDATE() + ' Q4'
    END
END

Error is:

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'END'.

Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'END'.

Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'END'.

Msg 156, Level 15, State 1, Line 18
Incorrect syntax near the keyword 'END'.

Upvotes: 0

Views: 3681

Answers (2)

Dave C
Dave C

Reputation: 7402

That is hard to read, and unfortunately riddled with syntax issues.

You're missing several parenthesis in the SET statements, and you can't concatenate an int and a varchar.

Try this...

DECLARE @PERIOD AS VARCHAR(7)

DECLARE @MONTHVAL INT, @YEARVAL VARCHAR(4)
SET @MONTHVAL = MONTH(GETDATE())
SET @YEARVAL = CONVERT(VARCHAR(4),YEAR(GETDATE()))

SET @PERIOD = CASE WHEN @MONTHVAL IN (1,2,3) THEN @YEARVAL + ' Q1'
                   WHEN @MONTHVAL IN (4,5,6) THEN @YEARVAL + ' Q2'
                   WHEN @MONTHVAL IN (7,8,9) THEN @YEARVAL + ' Q3'
                   WHEN @MONTHVAL IN (10,11,12) THEN @YEARVAL + ' Q4'
              END

SELECT @PERIOD

Upvotes: 5

Dan Walmsley
Dan Walmsley

Reputation: 2821

Okay so I think this is what you're looking for. 

declare @PERIOD AS VARCHAR(7)

IF( DATEPART(MONTH, GETDATE()) = 1 or  DATEPART(MONTH, GETDATE()) = 2 or  DATEPART(MONTH, GETDATE()) = 3)


    BEGIN
       SET @PERIOD = CAST (DATEPART(YEAR,GETDATE()) AS VARCHAR) + ' Q1'
    END
else IF( DATEPART(MONTH, GETDATE()) = 4 or  DATEPART(MONTH, GETDATE()) = 5 or  DATEPART(MONTH, GETDATE())= 6)
    BEGIN
       SET @PERIOD =  CAST (DATEPART(YEAR,GETDATE()) AS VARCHAR) + ' Q2'
    END
else IF( DATEPART(MONTH, GETDATE()) = 7 or  DATEPART(MONTH, GETDATE()) = 8 or  DATEPART(MONTH, GETDATE()) = 9)
    BEGIN
       SET @PERIOD =  CAST (DATEPART(YEAR,GETDATE()) AS VARCHAR) + ' Q3'
    END
else IF ( DATEPART(MONTH, GETDATE()) = 10 or  DATEPART(MONTH, GETDATE()) = 11 or  DATEPART(MONTH, GETDATE()) = 12)
    BEGIN
       SET  @PERIOD = CAST (DATEPART(YEAR,GETDATE()) AS VARCHAR)+ ' Q4'
    END

Sorry for the slack answer, limited time. I may flesh it out later but I hope this gets you up and running.

Upvotes: 2

Related Questions