CODEWITHSUNDEEP
sqlsql-serveruser-defined-functions
Ramon Fonis
Ramon Fonis

Reputation: 167

user defined function return statment in Scalar-Valued

I have a table with the following columns:

CREATE TABLE [dbo].[TblPerson](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](30) NULL,
    [Family] [nvarchar](40) NULL,
    [address] [nvarchar](200) NULL,
    [MeliCode] [char](10) NOT NULL,
    [IsActive] [bit] NULL CONSTRAINT [DF_TblPerson_IsActive]  DEFAULT ((0)),
    [Count] [int] NULL CONSTRAINT [DF_TblPerson_Count]  DEFAULT ((0)),
 CONSTRAINT [PK_TblPerson] PRIMARY KEY CLUSTERED 
(

Now i want to write a UDF which gets the Melicode as input parameter and if the IsActive = 1 then the UDF returns the value of count, else the UDF must return 0. i write the following function as

CREATE FUNCTION ReturnMelli ( @Melicode CHAR(10) )
RETURNS INT
AS 
    BEGIN
        DECLARE @RESULT BIT
        SET @RESULT = ( SELECT  Isactive
                        FROM    TblPerson
                        WHERE   MeliCode = @Melicode
                      )
        DECLARE @R INT
        SET @R = ( SELECT   [Count]
                   FROM     TblPerson
                   WHERE    MeliCode = @Melicode
                 )
        IF ( @RESULT = 1 ) 
            BEGIN
                RETURN @R
            END
        ELSE 
            BEGIN
                RETURN 0
            END

    END

Then, there is problem with running the UDF as 

Msg 455, Level 16, State 2, Procedure ReturnMelli, Line 17
The last statement included within a function must be a return statement.

i don't get the reason of failure. Even i put the return value at the end, it doesn't work

Upvotes: 0

Views: 451

Answers (2)

user5151179
user5151179

Reputation: 585

Replace this

    IF ( @RESULT = 1 ) 
        BEGIN
            RETURN @R
        END
    ELSE 
        BEGIN
            RETURN 0
        END

with this

    RETURN IIF(@RESULT = 1, @R, 0)

Upvotes: 2

Shiju Shaji
Shiju Shaji

Reputation: 1730

This link will Help you

Try like this ..

CREATE FUNCTION ReturnMelli ( @Melicode CHAR(10) )
    RETURNS INT
    AS 
        BEGIN
            DECLARE @RESULT BIT
            SET @RESULT = ( SELECT  Isactive
                            FROM    TblPerson
                            WHERE   MeliCode = @Melicode
                          )
            DECLARE @R INT
            SET @R = ( SELECT   [Count]
                       FROM     TblPerson
                       WHERE    MeliCode = @Melicode
                     )
            IF ( @RESULT <> 1 ) 
                BEGIN
                    SET @R =0
                END
                   RETURN @R

        END

Upvotes: 0

Related Questions