stored procedure returns nothing

Question

This stored procedure doesn't work. I've checked the SQL and it returns the correct value when parsed directly to the DB. It's really strange! It just returns 0 rows.

What could be wrong?

ALTER PROCEDURE dbo.GetSaltOfUser

 (
 @eMail nvarchar

 )

AS
DECLARE @result nvarchar
 /* SET NOCOUNT ON */
BEGIN
   SELECT @result = salt
   FROM UserSet
   WHERE eMail = @eMail
   RETURN @result
END

Answer 1

 @eMail nvarchar

Will truncate the passed in email to one character. You need to put in a length. e.g.

  @eMail nvarchar(50)

This should match the datatype of the relevant column in UserSet

Also do you really want to be using the return code for this or do you want to use an output parameter perhaps - or just a scalar select?

ALTER PROCEDURE dbo.GetSaltOfUser

    (
    @eMail nvarchar (50),      /*Should match datatype of UserSet.eMail*/
    @salt nvarchar (50) OUTPUT /*Should match datatype of UserSet.salt*/
  )

AS
BEGIN
    SET NOCOUNT ON

    SELECT @result = salt
    FROM UserSet
    WHERE eMail = @eMail
END

And to call it

DECLARE @salt nvarchar(50)
EXECUTE dbo.GetSaltOfUser N'[email protected]', @salt OUTPUT
SELECT @salt