Check transaction committed and if so return a value

Question

I want to insert a new user (Email address) into a table of users. If successful (no duplicate), insert basic configurations for new user to other tables, and return some data to client. If not successful, receive relevant notification in dataset in C# project.

Here is sample code so you can easily comment on it

ALTER PROCEDURE [dbo].[CreateUser] 
  @Email nvarchar(256),   
  @Password nvarchar(256)   
AS
BEGIN
  declare @UserID uniqueidentifier;
  declare @ConfigID uniqueidentifier;
  declare @TopMostNode uniqueidentifier;

  BEGIN TRANSACTION;
    select @UserID = NEWID();
    select @ConfigID = newid();

    insert into Users (UserID,Email,Password,CurrentConfig) 
      values(@UserID, @Email, @Password, @ConfigID);

    INSERT INTO Configs (ConfigID, OwnerID, DisplayName, LastPrintID)
      VALUES (@ConfigID,@UserID, 'Default Config', 1);

  COMMIT TRANSACTION 
END

Answer 1

That code is almost complete. Just need to add a SELECT to return the new keys. In case of an error, you will get an exception. EG:

ALTER PROCEDURE [dbo].[CreateUser] 
  @Email nvarchar(256),   
  @Password nvarchar(256)   
AS
BEGIN
  declare @UserID uniqueidentifier;
  declare @ConfigID uniqueidentifier;
  declare @TopMostNode uniqueidentifier;

  BEGIN TRANSACTION;
    select @UserID = NEWID();
    select @ConfigID = newid();

    insert into Users (UserID,Email,Password,CurrentConfig) 
      values(@UserID, @Email, @Password, @ConfigID);

    INSERT INTO Configs (ConfigID, OwnerID, DisplayName, LastPrintID)
      VALUES (@ConfigID,@UserID, 'Default Config', 1);

  COMMIT TRANSACTION 
  select @UserID UserID, @ConfigID ConfigID;
END