Validation Store Procedure

[Leon]

Is There a better way I could have written the following stored procedure?
Or is my way cool?

Checking: Username, Password, Activation = True

CREATE PROCEDURE GetAuthorization
(
@Username Varchar( 20 ),
@Password Varchar( 16 )
)
AS
DECLARE @AccountID INT
SELECT @AccountID = AccountID
FROM Account
WHERE Username = @username
AND Password = @Password AND Active = 1
IF @AccountID IS NOT NULL
Return @AccountID
ELSE
IF Exists( SELECT Username
FROM Account WHERE Username = @Username)
Return - 3
ELSE
IF Exists( SELECT Username
FROM Account WHERE Username = @Username AND Password = @Password AND
Active = 0 )
Return - 2
ELSE
RETURN - 1
GO

[bruce barker]

how about:
CREATE PROCEDURE GetAuthorization
@Username Varchar( 20 ),
@Password Varchar( 16 )
as
set nocount on
declare @AccountID int

set @AccountID = -1
select @AccountID =
case
when Password = @Password and Active = 1 then AccountID
when Password = @Password then -2
else -3
end
from Account
where Username = @Username

return @AccountID
go

actually your version would never return -2

-- bruce (sqlwork.com)

"Leon" <vn*****@msn.com> wrote in message
news:%2***************@TK2MSFTNGP09.phx.gbl...

Is There a better way I could have written the following stored procedure?
Or is my way cool?

Checking: Username, Password, Activation = True

CREATE PROCEDURE GetAuthorization
(
@Username Varchar( 20 ),
@Password Varchar( 16 )
)
AS
DECLARE @AccountID INT
SELECT @AccountID = AccountID
FROM Account
WHERE Username = @username
AND Password = @Password AND Active = 1
IF @AccountID IS NOT NULL
Return @AccountID
ELSE
IF Exists( SELECT Username
FROM Account WHERE Username = @Username)
Return - 3
ELSE
IF Exists( SELECT Username
FROM Account WHERE Username = @Username AND Password = @Password AND
Active = 0 )
Return - 2
ELSE
RETURN - 1
GO

[Leon]

Cool! Thanks.

"bruce barker" <no***********@safeco.com> wrote in message
news:uE**************@TK2MSFTNGP11.phx.gbl...

how about:
CREATE PROCEDURE GetAuthorization
@Username Varchar( 20 ),
@Password Varchar( 16 )
as
set nocount on
declare @AccountID int

set @AccountID = -1
select @AccountID =
case
when Password = @Password and Active = 1 then AccountID
when Password = @Password then -2
else -3
end
from Account
where Username = @Username

return @AccountID
go

actually your version would never return -2

-- bruce (sqlwork.com)

"Leon" <vn*****@msn.com> wrote in message
news:%2***************@TK2MSFTNGP09.phx.gbl...

Is There a better way I could have written the following stored
procedure?
Or is my way cool?

Checking: Username, Password, Activation = True

CREATE PROCEDURE GetAuthorization
(
@Username Varchar( 20 ),
@Password Varchar( 16 )
)
AS
DECLARE @AccountID INT
SELECT @AccountID = AccountID
FROM Account
WHERE Username = @username
AND Password = @Password AND Active = 1
IF @AccountID IS NOT NULL
Return @AccountID
ELSE
IF Exists( SELECT Username
FROM Account WHERE Username = @Username)
Return - 3
ELSE
IF Exists( SELECT Username
FROM Account WHERE Username = @Username AND Password = @Password AND
Active = 0 )
Return - 2
ELSE
RETURN - 1
GO