Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2005
    Posts
    10

    Unanswered: How to Create Store Procedure

    Hi all,

    I am not familiar with the Store Procedure, so just want to know how to create the store procedure? For example, i want to write a store procedure for Login validation to check whether the username and password is correct. So what should i do???

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Okay, first off I am FAMOUS for writing the kludgiest code on the planet. This proc may or may not work for you (it does for me). It may or may not be very fast. It may or may not meet with best practices guidelines (it probably doesn't). But it should give you some things to work with (a stored procedure, parameters, working with results). You need to do a bit of research on your own. Google is your friend.

    Supporting Tables
    Code:
    create table Users (Username varchar(255), [Password] varchar(255))
    Supporting Data
    Code:
    insert Users (UserName, [Password]) values ('tscott','123')
    go
    insert Users (UserName, [Password]) values ('ascott','abc')
    go
    insert Users (UserName, [Password]) values ('bscott','Abc')
    go

    select * from users2

    Stored Procedure
    Code:
    CREATE PROC spValidatePassword (    @UserName varchar(255),
        @Password varchar(255)
    )
    
    /*******************************************************
    *  spValidatePassword
    *  hmscott
    * 
    *  Validates a username/password against a table of known username/passwords
    * Warning: data is not encrypted; these passwords are not secure.  This
    * sample is for demonstration only and is not intended for production
    * use.
    * 
    * Parameters:
    *    IN: UserName   The usernmae provided by the user
    *         Password    The password provided by the user
    *
    * Returns:
    *    Single-row recordset (ReturnCode):
    *        -1:  UserName not found
    *         0:  Password incorrect for this user
    *         1:  Password correct for this user
    ******************************************************/
    
    
    AS
    
        DECLARE @LocalPassword varchar(255), @ReturnCode int
    
        SELECT @LocalPassword = [Password] 
        FROM dbo.Users2
        WHERE UserName = @UserName
    
        IF @LocalPassword IS NULL
        BEGIN
            SELECT @ReturnCode = -1 -- User not valid
        END
        ELSE
        BEGIN
            IF @LocalPassword = @Password COLLATE Latin1_General_CS_AS
            -- IF @LocalPassword = @Password -- Use this line instead of the above line if you want the passwords to be case insensitive
            BEGIN
                SELECT @ReturnCode = 1 -- Password matches
            END
            ELSE
            BEGIN
                SELECT @ReturnCode = 0 -- Password does not match
            END
        END
    
        SELECT @ReturnCode as ReturnCode

    spValidatePassword 'nouser', 'Foo'

    spValidatePassword 'bscott', 'Abc'

    spValidatePassword 'bscott', 'abc'
    Last edited by hmscott; 12-06-05 at 13:03.
    Have you hugged your backup today?

Posting Permissions

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