Monday, February 11, 2013 8:04 PMBelow 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
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.
- Edited by lochnesse Monday, February 11, 2013 8:11 PM
Monday, February 11, 2013 10:52 PM
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
- Marked As Answer by Iric WenModerator Wednesday, February 20, 2013 8:45 AM