SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to write a stored procedure to check if the members membership has expired


how to write a stored procedure to check if the members membership has expired

Author
Message
sanjay.dakolia
sanjay.dakolia
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 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
SpringTownDBA
SpringTownDBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1470 Visits: 1499
can you post what you have tried so far?
jameslester78
jameslester78
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 370
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
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213749 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213749 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213749 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Vedran Kesegic
Vedran Kesegic
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1952 Visits: 1266
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

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)SSC Guru (213K reputation)

Group: General Forum Members
Points: 213749 Visits: 41977
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Steven Willis
Steven Willis
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2015 Visits: 1721
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



 
Vedran Kesegic
Vedran Kesegic
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1952 Visits: 1266
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

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search