stored procedure returns nothing

8
votes

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
3
sqlsql-servertsqlstored-proceduresoop
From where I write it in Visual studio. I right click and choose Execute. – Phil
How are you determining the return value? – Martin Smith
wth, what's with the oops tag? I should have that on all of my questions.. "oops, I didn't know enough" :) – Phil

3 Answers

7
votes
 @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
1
votes

You do not need to assign the salt to a variable.

CREATE PROCEDURE dbo.GetSaltOfUser
(
    @eMail nvarchar    
)
AS
BEGIN
    SELECT salt
    FROM UserSet
    WHERE eMail = @eMail
END
1
votes

Two Cents from my end

  • Good you are using SET NOCOUNT ON
  • Always use RETURN to return status code, Example 0- success, 1 - failure
  • Use SELECT to return the ROWS
  • Use Try Catch to handle Error Conditions