A return value cannot be used in this context

Question

I have written 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 it has generated this error:

Msg 178, Level 15, State 1, Line 16
A RETURN statement with a return value cannot be used in this context.

What does this 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 the whole code for this function.

Answer 1

You need to understand that Return works only inside a function or stored procedure. If you are not using it inside a function or stored procedure then it is just a batch of select statements. So you need to use select instead of return.

SQL DEMO

EDIT:

Your edit after you modified it as a function works perfectly fine.

SQL DEMO

Answer 2

Try to use CASE instead of IF and remove 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

Check about how to CREATE FUNCTION