these SPs are killing me!

[bbolte]

here is the stored procedure (ms sql server):

Alter PROCEDURE "spLoginCheck"
/* checks password then if valid updates the lastLogin date field */
(
   @loginID  varchar(50),
   @PassWord varchar(20)
)
AS
   if exists(SELECT clientID
                 FROM login 
                 WHERE UserName = @loginID AND userPW = @PassWord)
      Begin
         UPDATE login 
         SET lastLogIn = GETDATE()
         WHERE LoginID = @loginID
      End
Return

i want this to check if the record exists, if it does update the login date and return the clientID value. the asp code i'm using is:

objRs.Open "exec spLoginCheck LoginID, Password", connNav
strClientID = objRs("clientID")
'If a client id was passed back
If strClientID <> "" Then
   Login = True
   'set up session variable for login checking on pages
   Session("clientID") = strClientID
Else
   Login = False
End If

nothing is being returned to the recordset as i get sent to my login error page. anyone have any thoughts?

also, any good ref books on the subject of stored procs?

[JimBolla]

the line

if exists(SELECT clientID
FROM login
WHERE UserName = @loginID AND userPW = @PassWord)

will NOT return the value clientID to the caller of the stored procedure. if you want to return the value, simply duplicate the select statement

Alter PROCEDURE "spLoginCheck"
/* checks password then if valid updates the lastLogin date field */
(
   @loginID  varchar(50),
   @PassWord varchar(20)
)
AS
    SELECT clientID
                 FROM login 
                 WHERE UserName = @loginID AND userPW 

   if exists(SELECT clientID
                 FROM login 
                 WHERE UserName = @loginID AND userPW = @PassWord)
      Begin
         UPDATE login 
         SET lastLogIn = GETDATE()
         WHERE LoginID = @loginID
      End
Return

that's the quick and dirty way. personally i don't like the duplication and would modify the code to store in a variable once, and return and check against that....didn't i already write this in another message thread?


books: i recommend one of wrox's SQL books (www.wrox.com)

[bbolte]

yes you did, but i was trying to get the client id out. and it gives me an ado error. here is yours:

Alter PROCEDURE "dbo.spLoginTotal"
/* checks password then if valid updates the lastLogin date field */
(@loginID  varchar(50), @strPassWord varchar(20))
AS
SET NOCOUNT ON

DECLARE @id int

SELECT @id =  loginID, clientID
FROM login
WHERE UserName = @LoginID AND userPW = @strPassWord

IF @id > 0 
UPDATE login
SET lastLogIn = GETDATE()
WHERE loginID = @id

the error says: a select statement that assigns a value to a variable must not be combined with data-retrieval operations.

[bbolte]

can you show me how you would rewrite that?

[JimBolla]

the problem is this:

SELECT @id = loginID, clientID
FROM login
WHERE UserName = @LoginID AND userPW = @strPassWord

unfortunately you can't do it quite this way. the error message is pretty descriptive in this case. basically what you can do is declare another variable and assign clientid to that and then just return that variable with its own select statement.