Conditional Checks -getting in over my head? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Conditional Checks -getting in over my head?

Author

Topic

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-10-31 : 06:30:37

Hi,

I'm relatively new to SQL Server and have only just started expanding my use of stored procedures (and loving every moment so far) however I’ve now come up to a bit of a brick wall for something that I’m sure is very simple. Basically I’m writing an authorisation application where the user is validated against a set of rather specific criteria. There are three tables involved:

AUTH_Users
-U_ID --PK
-C_ID --FK (AUTH _Campaign)
-U_Username
-U_Password
-U_Active
-U_Added_Date

AUTH_Clients
-CL_ID --PK
-CL_Name
-CL_Active

AUTH_Campaign
-C_ID --PK
-CL_ID --FK (AUTH _Campaign)
-C_Pass_Valid
-C_Pass_Exp_Hrs
-C_Pass_Exp_Days
-C_Pass_Exp_Mths
-C_Pass_Exp_Yrs
-C_Pass_Exp_Date
-C_Pass_Exp_Uses

Basically so far my SP uses a number or IF EXISTS(SELECT…) statements to step through whether the campaign is active etc and if at any point its failed returns 0 to the user. This is fine so far however the last step is to make sure that the user is within the limits of the campaign, the limits are set in [C_Pass_Valid] which contains DAYS, YEAR, MONT, DATE, USES etc. My idea was to use this value to reference the relevant column ie if it contains YEAR then I was thinking to do something like:

IF DATEADD(Year, [C_Pass_Exp_Yrs], [U_Added_Date]) < GetDate()
--User is ok, authorise
SELECT '1' AS [Resp]
ELSE
--user is not valid
SELECT '0' AS [Resp]

My problem is working out which value to use, I have been playing with a CASE statement but I’m not sure how to do it. What I need is to find out which column to reference, and with that column run a relative check (ie if its limited on the #uses check the number of logins <= allowed quota, if it’s a date, check its within the boundaries).

I hope I haven’t rambled too much, please let me know if you want any clarification.

Tim

ps. I'm using SQL Server 7 just incase there's a great function in 2000 ;)

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-10-31 : 06:45:19

On thinking a little further, would it be simpler to execute another Stored Procedure when it determines which value to use and have that return the True/False response? If so, would I just add EXC SPName after the Case?

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2004-10-31 : 06:48:06

I have a question,
in the AUTH_Campaign table why do you have so many expiration time columns ?
-C_Pass_Exp_Hrs
-C_Pass_Exp_Days
-C_Pass_Exp_Mths
-C_Pass_Exp_Yrs
-C_Pass_Exp_Date
would it not be enough to just have the column:
-C_Pass_Exp_Date
???

And could you not just compare -C_Pass_Exp_Date to GETDATE()
if GETDATE() <= -C_Pass_Exp_Date
-- then ok, current date is not past the users expiry date.

I might be missing some application logic though.

rockmoose

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-10-31 : 06:57:05

Hi,

The idea is that whoever is managing the campaign is able to set access limits on the "campaign" -this is for someones website where they offer a promotion say "get access for 15 days" type thing, when setting it up they simply select "Days" and type "15" into the admin console, so when the user signs up they have 15 days access from the date the signed up. They also need a way of offering a limited access function, say 10 logins hence why I couldn't just use a date.

I guess I could put that code at the registration SP so it sets the limits then but I've still got the problem of dates/uses and which date period to use...

I don't know perhaps I'm thinking about this all wrong, I've got so used to doing everything like this in code...

timgaunt
Posting Yak Master

115 Posts

Posted - 2004-11-01 : 04:47:06

I think I've sorted it now, what I've done (code below for reference) is used my SELECT CASE to run different calculations on the date/uses etc, if the value returned is positive (>0) then they are authorised, if the value isn't then they are denied. I'm doing this by converting the time/date to a numeric value (CAST as money).

What I would like to know is how incorrect is this? Can I be caught out like this?

CREATE PROCEDURE AUTH_CheckUser(
@U_ID As int,
@CL_ID As int,
@U_Tel_Num As char(12),
@U_Username As nvarchar(255),
@U_Password As char(255)
)
AS
DECLARE @Is_User_Valid As Money

--Check there is a user record for info supplied which is still active
IF EXISTS(SELECT U_ID FROM AUTH_Users WHERE U_ID = @U_ID AND U_Tel_Num = @U_Tel_Num AND U_Active = 1)
--User record does exist so match to a campaign/client and ensure both are still valid
IF EXISTS(SELECT U_ID FROM AUTH_Campaigns RIGHT JOIN AUTH_Clients ON AUTH_Clients.CL_ID = AUTH_Campaigns.CL_ID LEFT JOIN AUTH_Users ON AUTH_Users.C_ID = AUTH_Campaigns.C_ID WHERE (U_ID = @U_ID AND AUTH_Campaigns.CL_ID = @CL_ID AND CL_Active = 1 AND C_Active = 1))
BEGIN
--Both the campaign and client are still valid so check the user is within the constraints of the campaign
SELECT @Is_User_Valid = CASE C_Pass_Valid
WHEN 'HOUR' THEN CAST(DATEADD(hour, C_Pass_Exp_Hrs, U_Added_Date) AS money) - CAST(GetDate() AS money)
WHEN 'DAYS' THEN CAST(DATEADD(Day, C_Pass_Exp_Days, U_Added_Date) AS money) - CAST(GetDate() AS money)
WHEN 'MONT' THEN CAST(DATEADD(Month, C_Pass_Exp_Mths, U_Added_Date) AS money) - CAST(GetDate() AS money)
WHEN 'YEAR' THEN CAST(DATEADD(Year, C_Pass_Exp_Yrs, U_Added_Date) AS money) - CAST(GetDate() AS money)
WHEN 'DATE' THEN CAST(C_Pass_Exp_Date AS money) - CAST(GetDate() AS money)
ELSE C_Pass_Exp_Uses - U_Total_Logins
END
FROM AUTH_Users
LEFT JOIN AUTH_Campaigns ON AUTH_Users.C_ID = AUTH_Campaigns.C_ID
WHERE U_ID = @U_ID AND U_Tel_Num = @U_Tel_Num AND U_Active = 1
IF @Is_User_Valid>0
BEGIN
--TODO: Updated the login counts
UPDATE AUTH_Users SET U_Total_Logins = U_Total_Logins + 1 WHERE U_ID = @U_ID AND U_Tel_Num = @U_Tel_Num AND U_Active = 1
--Users account is ok return true (authorised)
SELECT '1' As [Resp]
END
ELSE
BEGIN
--Users account has expired or the campaign has expired, return false
SELECT '0' As [Resp]
END
END
ELSE
BEGIN
--Either the Campaign or the Client is no longer valid to return false
SELECT '0' As [Resp]
END
ELSE
BEGIN
--User record doesn't exist so return false
SELECT '0' As [Resp]
END
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources