April 23, 2012 at 10:51 am
Hi,
I Have a table that Include UserName and Password.
I need to build an example of SP that receive Username and Password and And checks for Validation .
April 23, 2012 at 10:54 am
What have you tried so far?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 23, 2012 at 11:00 am
Oh sorry to post cut in middle .
I will rewrite and ask again
Thank you.
April 23, 2012 at 11:01 am
In addition to what Sean said, what’s the issue you are facing? It pretty simple requirement subject to, you try for it.
April 23, 2012 at 11:25 am
I Have Table That include User name And Password.
The Sp should check if the username password is correct .
If it's Wrong five time in A Raw I need to Print a Massage.
April 23, 2012 at 11:27 am
Sounds like homework. We are not going to do your homework for you but we can help. What have you tried so far?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 24, 2012 at 7:13 am
Hi,
ALTER PROCEDURE
Casino.Players_Logins
@UName NVARCHAR(10),
@pass NVARCHAR(10)
AS
BEGIN TRY
DECLARE @count VARCHAR(10)
SET @count = 0
SET
@count =
(
SELECT
1
FROM
Casino.Members
WHERE
Password = @pass
)
IF
@count = 1
BEGIN
INSERT INTO
CASINO.PlayerSessions (PlayerId,LoginDateTime)
VALUES
((SELECT ID FROM CASINO.Members WHERE Username = @UName) , SYSDATETIME())
PRINT ('Welcome Back')
END
ELSE
BEGIN
PRINT ('Password is Wrong')
UPDATE CASINO.Members
set status = (select status from CASINO.Members where Username = @Uname ) + 1
where username = @uname
END
END TRY
BEGIN CATCH
PRINT ('Username Is WRONGG!!!!')
END CATCH
--- My Problem is that I need to count five tries in a Raw of wrong password---
--- And put in the status column after 5 tries the value 'block'
This is not so effective stored Procedure. any hint ? (not answer's because it's my homework :P)
Thank you
April 24, 2012 at 8:06 am
Thanks for being honest about it being homework. It helps make the response in such a way as to guide you instead of feeding you the answer. From your response it sounds like you prefer to learn it instead of getting the grade. That is awesome!!!
There are a couple of suggestions about how to proceed.
Instead of :
SET
@count =
(
SELECT
1
FROM
Casino.Members
WHERE
Password = @pass
)
IF
@count = 1
You should look at the exists clause. http://msdn.microsoft.com/en-us/library/ms188336%28v=sql.105%29.aspx
They way you have coded that will work but the exists is a bit easier to read and will likely perform better.
---------------------------
It looks like you are status from CASINO.Members to track the loginattempts? this may be outside of your ability being homework but the name status is entirely to vague. It is better to use more descriptive column names. To extend that one more step, if this were a real system I would suggest there be a separate table to hold failed login attempts. If you use a single field you can't tell me when the second failed attempt happened.
---------------------------
If you want consecutive failed attempts you need to consider what you want to do to the Members row on a successful login.
---------------------------
Looking even closer it looks like you are trying to use the status column to hold numeric and varchar data?
First you have this.
UPDATE CASINO.Members
set status = (select status from CASINO.Members where Username = @Uname ) + 1
This will work IF there is numeric data in this column. Of course it must do an implicit conversion.
But then later you said:
--- And put in the status column after 5 tries the value 'block'
That is going to cause all sorts of issues because you have mixed data in a single column.
Take some time to digest all this. Then do what you can to update your code. Feel free to post it back again if you want some more pointers.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 25, 2012 at 6:03 am
Thank you for you Response.
I'm trying it with the Exits but My Problem that I want to do in A way that for start he will check if username exists and than
if the password is right.
and btw. I build another table as you said, a lot more effective 10x!
April 25, 2012 at 6:21 am
SELECT
1
FROM
Casino.Members
WHERE
Password = @pass
)
you might want to check this slightly differently...... haven't you forgot to check if the username exists with that password.... if my password was "fred" and yours was "barney" then you will log in even if you try to log in with your username and my password
😉
MVDBA
April 25, 2012 at 6:50 am
Thank you very much. I solved the case for Now.!! Great site !
ALTER PROCEDURE
CASINO.LoginTry
@Name VARCHAR(32),
@pass VARCHAR(32)
AS
BEGIN
IF
EXISTS
(
SELECT
1
FROM CASINO.Members
WHERE
Username = @Name
)
BEGIN
IF
EXISTS
(
SELECT
1
FROM
CASINO.Members
WHERE
Password = @pass
AND
Username = @Name
)
PRINT ('GOOD PASS - WELCOME')
ELSE
PRINT ('WRONG PASSWORD 5 TRIES')
END
ELSE
BEGIN
PRINT ('Wrong user OR Password try again')
END
END
And it's will work only with original Password of user!!
10x
April 25, 2012 at 6:52 am
!!
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply