SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Exclamation these SPs are killing me!

    here is the stored procedure (ms sql server):
    Code:
    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:
    Code:
    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?

  2. #2
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    Code:
    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)

  3. #3
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes you did, but i was trying to get the client id out. and it gives me an ado error. here is yours:
    Code:
    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.
    Last edited by bbolte; May 16, 2002 at 15:18.

  4. #4
    SitePoint Wizard bbolte's Avatar
    Join Date
    Nov 2001
    Location
    The Central Plains
    Posts
    3,304
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can you show me how you would rewrite that?

  5. #5
    \m/ R.I.P. Dimebag! \m/ JimBolla's Avatar
    Join Date
    Dec 2001
    Location
    erie, pa
    Posts
    1,130
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •