Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to write a stored procedure to check if the members membership has expired Expand / Collapse
Author
Message
Posted Saturday, November 24, 2012 4:37 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 16, 2012 11:53 PM
Points: 9, Visits: 17
i have a table named users where i have two three column userid, email, membershipexpiry

i want to write a stored procedure tat will take email as a input from the front hand and compare the current date with the date stored int the membershipexpiry column in the database its should be less then or equal to the current date if the membership hasn't expired it should return 1 or else 0.
plz help me
thanks in advance
sanjay
Post #1388300
Posted Saturday, November 24, 2012 8:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, April 1, 2014 3:26 PM
Points: 316, Visits: 1,497
can you post what you have tried so far?
Post #1388309
Posted Saturday, November 24, 2012 9:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, November 25, 2014 9:01 AM
Points: 37, Visits: 223
create database example
GO
USE example
GO
Create table tbl_a (email varchar (20),membershipexpiry date)

insert into tbl_a VALUES
('a@a.com','01-Jan-2012'),('b@a.com','01-Jan-2013')

GO

Create proc proc_a
@email varchar(20)
as
declare @v date =

(select membershipexpiry from tbl_a where email = @email)

if @v<GETDATE() select 1 as expired

if @v>GETDATE() select 0 as expired



Go

exec proc_a @email = 'a@a.com'
exec proc_a @email = 'b@a.com'
GO
use master
drop database example
Post #1388312
Posted Saturday, November 24, 2012 2:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 35,609, Visits: 32,200
sanjay.dakolia (11/24/2012)
i have a table named users where i have two three column userid, email, membershipexpiry

i want to write a stored procedure tat will take email as a input from the front hand and compare the current date with the date stored int the membershipexpiry column in the database its should be less then or equal to the current date if the membership hasn't expired it should return 1 or else 0.
plz help me
thanks in advance
sanjay


Is the membership table using any form of "Slowly Changing Dimension"?


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1388335
Posted Saturday, November 24, 2012 2:58 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 35,609, Visits: 32,200
jameslester78 (11/24/2012)
create database example
GO
USE example
GO
Create table tbl_a (email varchar (20),membershipexpiry date)

insert into tbl_a VALUES
('a@a.com','01-Jan-2012'),('b@a.com','01-Jan-2013')

GO

Create proc proc_a
@email varchar(20)
as
declare @v date =

(select membershipexpiry from tbl_a where email = @email)

if @v<GETDATE() select 1 as expired

if @v>GETDATE() select 0 as expired



Go

exec proc_a @email = 'a@a.com'
exec proc_a @email = 'b@a.com'
GO
use master
drop database example


Normally, a "1" as a return is considered to be a "Yes" or "True" answer. My recommendation would be to change the name of the returned column from "expired" to "IsActive" just to avoid any confusion down the road.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1388337
Posted Saturday, November 24, 2012 3:08 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 35,609, Visits: 32,200
sanjay.dakolia (11/24/2012)
i have a table named users where i have two three column userid, email, membershipexpiry

i want to write a stored procedure tat will take email as a input from the front hand and compare the current date with the date stored int the membershipexpiry column in the database its should be less then or equal to the current date if the membership hasn't expired it should return 1 or else 0.
plz help me
thanks in advance
sanjay


Sanjay...

It looks like you're really new to SQL Server so I have to warn you... be careful with any script that does a DROP in the code unless it's dropping a table whose name starts with a "#" sign. If you leave out any of the code during a run, you could end up dropping the real "Users" table by mistake.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1388339
Posted Saturday, November 24, 2012 7:15 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
It's better to write a function, even better a table-valued inline function, and even better a view:

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)


It has much better performance than a procedure, but if you must use a procedure you can use the same expression used in a vew.
It would be a procedure with a single command: select.
HTH.


_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1388356
Posted Sunday, November 25, 2012 6:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 5:34 PM
Points: 35,609, Visits: 32,200
Vedran Kesegic (11/24/2012)

It has much better performance than a procedure, but if you must use a procedure you can use the same expression used in a vew.


You know, of course, that when you make such claims, you should prove it. Otherwise it's just hearsay that could be wrong.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1388393
Posted Monday, November 26, 2012 7:11 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Sunday, September 29, 2013 1:24 AM
Points: 429, Visits: 1,721
Here's an example of a procedure that takes an email address as input and outputs 0/1 if the user is expired. Of course, you can update the user's IsExpired column within the procedure itself or use this output to do that or whatever you need to do with the status info.

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


The output:
UserEmail	IsExpired
test4@aol.com 0

UserEmail IsExpired
test5@aol.com 1

 
Post #1388909
Posted Monday, November 26, 2012 11:55 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, September 29, 2014 5:52 AM
Points: 369, Visits: 1,215
You can do it in a single command:

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



_____________________________________________________
Microsoft Certified Master: SQL Server 2008
XDetails Addin - for SQL Developers
blog.sqlxdetails.com - Transaction log myths
Post #1388976
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse