Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004
    Posts
    25

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

    Code:
    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
    GO

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Make 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

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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