Friday, March 30, 2012

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?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

create table Users (Username varchar(255), [Password] varchar(255))

Supporting Data

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

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'sql

No comments:

Post a Comment