Reputation: 167
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
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
Reputation: 1730
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