CODEWITHSUNDEEP
sql-servert-sql
sharcfinz
sharcfinz

Reputation: 465

If else statement in SQL Server function won't compile

I want to see if a date occurs within the last year, and if so, then increase a value by 1. So I tried the following, but it returns the following error:

Msg 102, Level 15, State 1, Procedure IsComplete, Line 41 [Batch Start Line 7]
Incorrect syntax near '@compliant'.

Code:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER FUNCTION [dbo].[IsComplete] (@input VARCHAR(9))
RETURNS CHAR(1)
AS 
BEGIN
    DECLARE @idnum VARCHAR(9)
    DECLARE @complete CHAR
    DECLARE @cert int
    DECLARE @compliant int

    SET @idnum = SUBSTRING(@input, 0, 9)
    SET @compliant = 0

    SET @cert = (SELECT DATEDIFF(month, CertifiedDate, GETDATE()) 
                 FROM Profile 
                 WHERE IDNumber = @idnum)

    BEGIN
        IF @cert > 11
        BEGIN
            @compliant = @compliant + 1
        END
        ELSE
        BEGIN
            -- do nothing
        END
    END 

    RETURN @complete
END

Seems simple, but I just can't get this to work, in that it gives errors and won't compile. Can anyone point me in the right direction please?

Upvotes: 0

Views: 302

Answers (4)

Vahid Farahmandian
Vahid Farahmandian

Reputation: 6566

According to the error, in line 25 of the function, you have just forget to add SET. So change it as below:

SET @compliant = @compliant + 1

Upvotes: 1

Bhavin Gosai
Bhavin Gosai

Reputation: 211

Try following query: 

USE [CDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[IsComplete] (@input VARCHAR(9))
RETURNS CHAR(1)

AS BEGIN
DECLARE @idnum VARCHAR(9)
DECLARE @complete CHAR
DECLARE @cert int
DECLARE @compliant int

SET @idnum = SUBSTRING(@input, 0, 9)
SET @compliant = 0

SET @cert = (SELECT DATEDIFF(month, CertifiedDate, GETDATE()) FROM Profile WHERE IDNumber = @idnum)

BEGIN
    IF @cert > 11
        BEGIN
            SET @compliant = @compliant + 1
        END
    --ELSE
    --    BEGIN
    --        -- do nothing
    --    END
END 

RETURN @complete

END

Explanation:

  • I did only 2 small changes with your query:

    1. Added SET to the line @compliant = @compliant + 1
    2. Commented unwanted ELSE block.

  • You can obviously simplify your logic later if you want. Though, it's better to fix the error first.

See if it works.

Upvotes: 0

Bohemian
Bohemian

Reputation: 425168

Apply the KISS principle:

SET @compliant = @compliant + 
    (SELECT
      case when DATEDIFF(month, CertifiedDate, GETDATE()) > 11 then 1 else 0 end
     FROM Profile
     WHERE IDNumber = @idnum)

Upvotes: 3

Adi219
Adi219

Reputation: 4814

This is because your Else clause is empty, and SQL doesn't like empty clauses.

Just remove the whole 'Else' section (as it's doing nothing), and your query should compile :)

Upvotes: 0

Related Questions