Stored Procedure RegisterUser() help

Question

Below is the following RegisterUser() stored procedure that i have created and want to use from my MVC website that will call this when a user wants to register for my website.

/****** Object:  StoredProcedure [User].[RegisterUser]    Script Date: 2/11/2013 2:00:17 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROC [User].[RegisterUser]
	@UserName AS NVARCHAR(20),
	@Password AS NVARCHAR(20),
	@Email AS NVARCHAR(30),
	@FirstName AS NVARCHAR(15),
	@LastName AS NVARCHAR(15),
	@Failure AS NVARCHAR(256) OUTPUT
AS


-- Is UserName Taken
IF EXISTS( SELECT * FROM [User].[Users] WHERE UserName LIKE @UserName ) BEGIN
	SET @Failure = 'UserName is already taken';
	RETURN 1;
END

-- Is Email Taken
IF EXISTS( SELECT * FROM [User].[Users] WHERE Email LIKE @Email ) BEGIN
	SET @Failure = 'Email is already taken';
	RETURN 2;
END

BEGIN TRAN
-- Begin Creating New User

-- Create Salt
DECLARE @Salt AS VARBINARY(256) = NEWID();

-- Create Hash with Salt
DECLARE @HashedPassword AS  VARBINARY(max) = HASHBYTES('SHA1', CAST(@Password AS VARBINARY(40)) + @Salt);

-- Create User
INSERT INTO [User].[Users]([UserName], [Password], [Salt], [Email], [FirstName], [LastName])
VALUES (@UserName, @HashedPassword, @Salt, @Email, @FirstName, @LastName);

COMMIT TRAN
-- Success
RETURN 0; 

What is the recommended way of returning information back to indicate a username/Email is taken.  ATM i was thinking of just returning false if function fails or true otherwise.  If function fails it will return a Failure string ( as an IN/OUT  parameter ) that will indicate the exact reason of failure.

Answer 1

The use of a out parameter as failure string makes often no sense as it normally would not be displayed to the user directly. Imho the use of a return value is sufficient.

But your INSERT may fail even when you've checked the values of user name and e-mail before (due to the used isolation level). I would prefer an explicit error handling as you must have unique indices on UserName and Email like

BEGIN TRY
  -- ..
  -- INSERT..
  RETURN 0;
END TRY
BEGIN CATCH
  -- Check ERROR_MESSAGE() for the index names
  -- and return an appropriate return value > 0.
END CATCH