CREATE VIEW view_a as
SELECT a.email, a.membershipexpiry,
	Expired = CASE WHEN a.membershipexpiry < GETDATE() THEN 1 ELSE 0 END
FROM dbo.tbl_a a
GO

SELECT * FROM view_a

email                membershipexpiry Expired
-------------------- ---------------- -----------
a@a.com              2012-01-01       1
b@a.com              2013-01-01       0

(2 row(s) affected)

IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable

CREATE TABLE #TempTable (
    [ID] INT IDENTITY(1,1) NOT NULL,
    [Email] NVARCHAR(50) NULL,
    [ExDate] DATE NULL,
    [isExpired] BIT NULL,
    PRIMARY KEY (ID),
    UNIQUE (ID))

INSERT INTO #TempTable
SELECT 'test1@aol.com','2012-11-30',0 
UNION
SELECT 'test2@aol.com','2012-12-31',0
UNION
SELECT 'test3@aol.com','2013-01-31',0
UNION
SELECT 'test4@aol.com','2013-02-28',0
UNION
SELECT 'test5@aol.com','2012-10-31',1

CREATE PROCEDURE dbo.GetExpirationValue
     @UserEmail NVARCHAR(50)
    ,@IsExpired BIT OUTPUT
AS
BEGIN

    SET NOCOUNT ON    

    SELECT 
        @IsExpired =
        (CASE
            WHEN DATEDIFF(DAY,GETDATE(),ExDate) < 0 THEN 1
            ELSE 0
         END)
    FROM 
        #TempTable
    WHERE
        Email = @UserEmail
        
    SELECT 
         @UserEmail AS UserEmail
        ,@IsExpired AS IsExpired

END

/* Run the procedure */

DECLARE @UserExpired BIT

EXEC dbo.GetExpirationValue 
     @UserEmail = 'test4@aol.com'
    ,@IsExpired = @UserExpired OUTPUT

EXEC dbo.GetExpirationValue 
     @UserEmail = 'test5@aol.com'
    ,@IsExpired = @UserExpired OUTPUT

UserEmail	IsExpired
test4@aol.com	0

UserEmail	IsExpired
test5@aol.com	1

CREATE PROCEDURE dbo.GetExpirationValue
     @UserEmail VARCHAR(50)
AS
SELECT a.email, a.membershipexpiry,
	IsExpired = CASE WHEN a.membershipexpiry < GETDATE() THEN 1 ELSE 0 END
FROM dbo.tbl_a a
WHERE a.email = @UserEmail
GO