Monday, February 20, 2012

Problem with output parameter in SP

I am having problems returning the value of a parameter I have set in my stored procedure. Basically this is an authentication to check username and password for my login page. However, I am receiving the error:

Procedure 'DBAuthenticate' expects parameter '@.@.ID', which was not supplied.

This stored procedure is supposed to return a -1 if the username is not found, -2 if the password does not match, or the @.ID parameter, which is the user ID, if it is successful. How do i go about fixing this SP so that I am returning this output for @.ID?

CREATE PROCEDURE DBAuthenticate

(

@.UserName nVarChar (20),
@.Password nVarChar (20),
@.@.ID varchar(4) OUTPUT

)

AS
Declare @.ActualPassword nVarchar (20)

Select

@.@.ID = RegionID,

@.ActualPassword =regpassword

From dbo.Regions

Where Region = @.Username

If @.@.ID is not null
Begin
if @.Password =@.actualpassword

Select @.@.ID
Else

Select -2
End
Else

Select -1
GOMake sure that you specify OUTPUT in your EXECUTE call. If either the caller or the called routine fail to specify OUTPUT, the value isn't returned.

-PatP|||A couple of questions/things:

1. Why do you want to return something that your code already knows about? Return 1 instead.
2. Naming your parameter with @.@.xxx would result in server knowing it as @.xxx, not xxx as expected. And it doesn't make your parameter a "global" variable either.
3. Based on your logic @.ID variable will ALWAYS have whatever value was retrieved from Region table based on @.UserName or NULL, regardless of whether authentication was successful or not. You probably need to change the path of your authentication algorythm. How about setting it to NULL even if it exists but the password is wrong?

No comments:

Post a Comment