?
Solved

HELP - IF Statement and Stored Procedure

Posted on 2005-04-05
14
Medium Priority
?
487 Views
Last Modified: 2008-01-09
I'd like to set up a Stored Procedure so that when a user attempts to login, if no match is found it writes the variables to a Failed Logins table, but if a match is found, then rights the information to the Logins table.

For example...(I know this is wrong)

CREATE PROCEDURE sp_login
      @sUser char(12),
      @sPass char(12),
      @sIP char(16)
AS
SELECT username, pass
      FROM tblUsers
      WHERE username = @sUser AND pass=@sPass
If EXISTS BEGIN      
      INSERT INTO tlbLogins(username,pass,[date],ip)
      VALUES(@sUser,@sPass,GetDate(),@sIP)
      END
ELSE BEGIN
      INSERT INTO tblLoginsFailed(username,pass,[date],ip)
      VALUES(@sUser,@sPass,GetDate(),@sIP)
      END
GO


Thanks!


An afterthought (this might need to be a seperate question)....Right now I have my ASP checking to see if a match was found.  If I have the Stored Procedure check to see if a match is found, how will I modify the Stored Procedure to return a value of successful or not successful to my ASP Script? Below is the current script I use (minus the modified Stored Procedure attempt above)

sSql = "sp_login'" & sUser & "','"  & sPass  &"','" & sIP &"'"
conn.execute (sSQL)
If Rs.EOF Then
 ''''Do something
Else
 ''''Do something
End If




0
Comment
Question by:BendOverIGotYourBack
  • 7
  • 5
  • 2
14 Comments
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13712670
CREATE PROCEDURE sp_login
     @sUser char(12),
     @sPass char(12),
     @sIP char(16)
AS
SELECT username, pass
FROM tblUsers
WHERE username = @sUser AND pass=@sPass

If EXISTS (Select 1 FROM tblUsers WHERE username = @sUser AND pass=@sPass)
BEGIN    
     INSERT INTO tlbLogins(username,pass,[date],ip)
     VALUES(@sUser,@sPass,GetDate(),@sIP)
     Return 1       -- Successful
END
ELSE
   BEGIN
     INSERT INTO tblLoginsFailed(username,pass,[date],ip)
     VALUES(@sUser,@sPass,GetDate(),@sIP)
     Return 0        -- Not successful
  END
GO

In ASP read the @RETURN_VALUE parameter using ADO
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13712675
Let's try that again:

CREATE PROCEDURE sp_login
     @sUser char(12),
     @sPass char(12),
     @sIP char(16)
AS

SET NOCOUNT ON

If EXISTS (Select 1 FROM tblUsers WHERE username = @sUser AND pass=@sPass)
BEGIN    
     INSERT INTO tlbLogins(username,pass,[date],ip)
     VALUES(@sUser,@sPass,GetDate(),@sIP)
     Return 1       -- Successful
END
ELSE
   BEGIN
     INSERT INTO tblLoginsFailed(username,pass,[date],ip)
     VALUES(@sUser,@sPass,GetDate(),@sIP)
     Return 0        -- Not successful
  END
GO
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13712681
P.S.  You may be better off using one table for both.  Just have a flag to indicate successful or not.
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 2

Expert Comment

by:curlypinhead
ID: 13712698
You were close on the procedure:

CREATE PROCEDURE sp_login
     @sUser char(12),
     @sPass char(12),
     @sIP char(16)
AS
If EXISTS(SELECT true FROM tblUsers WHERE username = @sUser AND pass=@sPass) BEGIN    
     INSERT INTO tlbLogins(username,pass,[date],ip)
     VALUES(@sUser,@sPass,GetDate(),@sIP)
     SELECT 1 as 'userExists'
     END
ELSE BEGIN
     INSERT INTO tblLoginsFailed(username,pass,[date],ip)
     VALUES(@sUser,@sPass,GetDate(),@sIP)
     SELECT 0 as 'userExists'
     END
GO



on the asp page do this:

sSql = "sp_login'" & sUser & "','"  & sPass  &"','" & sIP &"'"
Rs = conn.execute (sSQL)

Rs.Open()

If Rs("userExists") = 1 Then
 ''''Do something
Else
 ''''Do something
End If

I think the asp syntax is right but I can't remember right now.  Check it out.

curlypinhead

0
 
LVL 2

Expert Comment

by:curlypinhead
ID: 13712703
I guess I'm a slow typer today :)
0
 

Author Comment

by:BendOverIGotYourBack
ID: 13718805
acperkins, your suggestion resulted in the error below.  I've tried cutting out the If statement issues and just selecting from and inserting to the tblLogins w/out checking for the username, and it works just fine. I've also taken out the return portion, and that still results in the same error.  Thoughts?

"Microsoft OLE DB Provider for ODBC Drivers error '80040e37'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tlbLogins'.
/includes/authentication/verify.asp, line 14"

<%
sUser = Request.Form("login")
sPass = Request.Form("pass")
sIP = Request.ServerVariables("REMOTE_ADDR")
sSql = "sp_loginS2'" & sUser &"','" & sPass & "','" & sIP &"'"
set rs = conn.execute(sSql)
%>








curlypinhead, for some reason your suggestion results in a syntax error. "Error 207: Invalid column name true"  Thoughts?


0
 
LVL 2

Accepted Solution

by:
curlypinhead earned 1000 total points
ID: 13718924
hey,
  yeah, put single quotes around true (i.e. 'true').  you could also use the number 1 if you wanted to (i.e. if exists (select 1 from tblUsers where...)).

The reason you are getting the tlbLogins error is because there is a syntax error in the code.  (I put the same syntax error in my code too, by the way).  It should be:

CREATE PROCEDURE sp_login
     @sUser char(12),
     @sPass char(12),
     @sIP char(16)
AS
If EXISTS(SELECT true FROM tblUsers WHERE username = @sUser AND pass=@sPass) BEGIN    
     INSERT INTO tblLogins(username,pass,[date],ip)
     VALUES(@sUser,@sPass,GetDate(),@sIP)
     SELECT 1 as 'userExists'
     END
ELSE BEGIN
     INSERT INTO tblLoginsFailed(username,pass,[date],ip)
     VALUES(@sUser,@sPass,GetDate(),@sIP)
     SELECT 0 as 'userExists'
     END
GO

if you want to do the return statement instead, the stored procedure would be:

CREATE PROCEDURE sp_login
     @sUser char(12),
     @sPass char(12),
     @sIP char(16)
AS

SET NOCOUNT ON

If EXISTS (Select 1 FROM tblUsers WHERE username = @sUser AND pass=@sPass)
BEGIN    
     INSERT INTO tblLogins(username,pass,[date],ip)
     VALUES(@sUser,@sPass,GetDate(),@sIP)
     Return 1       -- Successful
END
ELSE
   BEGIN
     INSERT INTO tblLoginsFailed(username,pass,[date],ip)
     VALUES(@sUser,@sPass,GetDate(),@sIP)
     Return 0        -- Not successful
  END
GO

curlypinhead
0
 

Author Comment

by:BendOverIGotYourBack
ID: 13719130
Well done curlypinhead, worked great!  Still kind of curious why acperkins suggestion didn't work...
0
 
LVL 2

Expert Comment

by:curlypinhead
ID: 13719171
it was just a syntax error.
originally it said:

insert into tlbLogins.....

it needed to be

insert into tblLogins....

otherwise, both suggestions would work fine.

curlypinhead
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13719270
>>Still kind of curious why acperkins suggestion didn't work...<<
Take a second look the solution accepted is exactly the same as my original one. The only difference is that in your original question you evidently mistyped the table name (you used tlbLogins).  Apparently the table name is tblLogins.  I had no way of knowing that.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13719286
>>Take a second look the solution accepted is exactly the same as my original one.<<
Even the comments are the same :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13719325
curlypinhead,

>>it was just a syntax error.<<
No, that is not a syntax error.  Otherwise the T-SQL compiler would catch it, wouldn't it?

On the other hand, "Insret" instead of "Insert" is a syntax error.
0
 
LVL 2

Expert Comment

by:curlypinhead
ID: 13721670
acperkins,
yes
I did use your code.  BendOverIGotYourBack  asked why it didn't work, so I was just repeating without the spelling mistake so that it would work.  I was presenting both options without spelling mistakes: yours and mine.

and as for syntax error,  i suppose you are right.  It is indeed just a typo.  sorry for any confusion.

curlypinhead
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13723457
No problems.  My gripe was with the questioner, not with you.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question