How do i do ?

CREATE PROCEDURE SP_Login
(
@.Email Varchar(60) = '',
@.Password Varchar(100) = ''
)
As
Declare @.UserEmail Int
Set Nocount On
If not exists( Select CdUser From User Where Email = @.Email And Password =
@.Password)
Begin
Raiserror(13001,18,1) --User not found
End
it should continues with a else to get the CdUser...I tryed:
Else
Begin
Set @.UserEmail = Select CdUser From User Where Email = @.Email
End
I'm trying to accomplish this, but doesn' work, What is the way ?You need a return statement to stop execution of the SP in the fiirst case..
.
and just setting the @.UserEmail variable does nopt return it to the client,
if that's what you're trying to do. Also, you can return different error
messages if the user exists and the password is wrong, if you want...
CREATE PROCEDURE SP_Login
@.Email Varchar(60) = '',
@.Password Varchar(100) = ''
As
Set Nocount On
Declare @.Err Integer
Declare @.Msg VarChar(500)
Set @.Msg = 'User: '+ @.Email + ' not found.'
If not Exists
(Select * From User
Where Email = @.Email)
Goto ErrHandler
-- ---
Set @.Msg = 'Incorrect password for User: '+ @.Email
If Not Exists
(Select * From User
Where Email = @.Email
And Password = @.Password)
Goto ErrHandler
-- --
-- Sp Only gets here if it passes
-- validation checks above
Select CdUser From User -- This returns CDUser to client
Where Email = @.Email
-- --
Return(0) -- This terminates SP processing
-- --
-- -- Rest only runs if error occored
ErrHandler:
Raiserror(@.Msg, 16,1)
Return(-1)|||Better not to raise an error at all - errors are for exceptional situations,
someone typing invalid login credentials is an expected situation. You could
return @.msg as an output param, look at the return value of the proc, or som
e
other method on the client rather than have to catch an exception.
KH
"CBretana" wrote:

> You need a return statement to stop execution of the SP in the fiirst case
..
> and just setting the @.UserEmail variable does nopt return it to the client
,
> if that's what you're trying to do. Also, you can return different error
> messages if the user exists and the password is wrong, if you want...
> CREATE PROCEDURE SP_Login
> @.Email Varchar(60) = '',
> @.Password Varchar(100) = ''
> As
> Set Nocount On
> Declare @.Err Integer
> Declare @.Msg VarChar(500)
>
> Set @.Msg = 'User: '+ @.Email + ' not found.'
> If not Exists
> (Select * From User
> Where Email = @.Email)
> Goto ErrHandler
> -- ---
> Set @.Msg = 'Incorrect password for User: '+ @.Email
> If Not Exists
> (Select * From User
> Where Email = @.Email
> And Password = @.Password)
> Goto ErrHandler
> -- --
> -- Sp Only gets here if it passes
> -- validation checks above
> Select CdUser From User -- This returns CDUser to client
> Where Email = @.Email
> -- --
> Return(0) -- This terminates SP processing
> -- --
> -- -- Rest only runs if error occored
> ErrHandler:
> Raiserror(@.Msg, 16,1)
> Return(-1)