November 24, 2012 at 4:37 am
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
November 24, 2012 at 8:43 am
can you post what you have tried so far?
November 24, 2012 at 9:01 am
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-2 date =
(select membershipexpiry from tbl_a where email = @email)
if @v-2<GETDATE() select 1 as expired
if @v-2>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
November 24, 2012 at 2:47 pm
sanjay.dakolia (11/24/2012)
i have a table named users where i have two three column userid, email, membershipexpiryi 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
Change is inevitable... Change for the better is not.
November 24, 2012 at 2:58 pm
jameslester78 (11/24/2012)
create database exampleGO
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-2 date =
(select membershipexpiry from tbl_a where email = @email)
if @v-2<GETDATE() select 1 as expired
if @v-2>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
Change is inevitable... Change for the better is not.
November 24, 2012 at 3:08 pm
sanjay.dakolia (11/24/2012)
i have a table named users where i have two three column userid, email, membershipexpiryi 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
Change is inevitable... Change for the better is not.
November 24, 2012 at 7:15 pm
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.
November 25, 2012 at 6:51 am
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
Change is inevitable... Change for the better is not.
November 26, 2012 at 7:11 pm
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:
UserEmailIsExpired
UserEmailIsExpired
November 26, 2012 at 11:55 pm
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply