title: Stored procedure for adding an application user MS SQL 2012
body: <p>I am trying to write a stored procedure for one of the queries for adding users into a table. I am having difficulty capturing the return values during success and failure. </p>
  
  <p>The username column in the User table is defined to have a unique constraint.</p>
  
  <p>This is the outline of the stored procedure.
  Stored Procedure Name: CreateUser</p>
  
  <p>Parameters</p>
  
  <p>@username varchar(64)</p>
  
  <p>@firstname varchar(64)</p>
  
  <p>@lastname varchar(64)</p>
  
  <p>@MD5Password varchar(64)</p>
  
  <p>@Access_Control int</p>
  
  <p>Returns Identity, -1 if user already exists or (-@@ERROR) (Integer)</p>
  
  <p>Below is the stored procedure that I have written but I can't seem to capture the return value part.</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[CreateUser] 
   -- Add the parameters for the stored procedure here
  @username varchar(64) = NULL, 
  @firstname varchar(64) = NULL,
  @lastname varchar(64) = NULL,
  @MD5Password varchar(255) = NULL,
  @Access_Control int = -10
  AS
  DECLARE @return_code int
  BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;
  
  
  -- Insert statements for procedure here
  INSERT INTO [dbo].[User] ([username], [firstname], [lastname], [password], [access_control]) VALUES (@username, @firstname, @lastname, @MD5Password, @Access_Control)
   IF(@@ERROR &lt;&gt; 0 )
      BEGIN
          PRINT N'User with this name already exists. Username must be unique' + @@ERROR
          SET @return_code = -1
      END
   ELSE
      BEGIN
          SET @return_code = SCOPE_IDENTITY()
      END
  
  RETURN @return_code
  
  END
  </code></pre>
  
  <p>I want to set the return value to the Identity in case of success. -1 if the user already exists or to some Error Value if some internal error occurs. How can I capture this behavior in the stored procedure. Thanks in advance.</p>