In this context, you cannot use the return value

Question

I wrote a function that returns 1 if the date difference is already greater than or equal to 30.

Here is my code:

DECLARE @flag int
DECLARE @isactive bit
DECLARE @date  Datetime

SET @flag = 0

SELECT @isactive = [m_isactive] 
FROM dbo.rms_month_email 
WHERE m_id = 3

SELECT @date = [m_createddate] 
FROM dbo.rms_month_email 
WHERE m_id = 3

IF (@isactive = 1 AND DATEDIFF(dd,@date,GETDATE()) = 30)
   SET  @flag = 1

RETURN @flag

but he generated this error:

Msg 178, Level 15, State 1, Line 16
In this context, you cannot use the RETURN operator with a return value.

What does it mean?

CREATE FUNCTION [dbo].[udf_isBonus]
(@m_id AS INT)
RETURNS INT
AS
BEGIN
    -- Declare the return variable here
    DECLARE @flag AS int
    DECLARE @isactive AS bit
    DECLARE @date AS Datetime

    SET @flag = 0

    SELECT @isactive = [m_isactive] FROM dbo.rms_month_email WHERE m_id = @m_id
    SELECT @date = [m_createddate] FROM dbo.rms_month_email WHERE m_id = @m_id

    IF (@isactive = 1 AND DATEDIFF(dd,@date,GETDATE()) = 30)
         SET @flag = 1

    RETURN @flag
END
GO

This is all the code for this function.

Answer 1

You need to understand what Returnworks only inside a function or stored procedure. If you do not use it inside a function or stored procedure, this is just a batch of selected statements. Therefore you need to use selectinstead Return.

SQL DEMO

EDIT:

, , .

SQL DEMO

Answer 2

CASE IF SET @flag = 0:

USE [petc_rms]
GO

 /****** Object:  UserDefinedFunction [dbo].[udf_isBonus]    Script Date:     9/29/2015 3:35:49 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[udf_isBonus]
(
 -- Add the parameters for the function here

    @m_id AS INT

)
RETURNS INT
AS
BEGIN
-- Declare the return variable here
DECLARE @flag AS int
DECLARE @isactive AS bit
DECLARE @date AS Datetime


    SELECT @isactive = [m_isactive] FROM dbo.rms_month_email WHERE m_id = @m_id
    SELECT @date = [m_createddate] FROM dbo.rms_month_email WHERE m_id = @m_id

    SET @flag = CASE WHEN @isactive = 1 AND DATEDIFF(dd,@date,GETDATE()) = 30 THEN 1 ELSE 0 END    

Return @flag

END

GO

, CREATE FUNCTION