title: RAISERROR And ROLLBACK TRANSACTION
body: <p>I want to use the sp as following in asp.net page.</p>
  
  <p>How can I show the Error messages in sp into asp.net ?</p>
  
  <p>Any DAL property sample to do the task in asp.net by calling the sp ?</p>
  
  <p>Is there any conflict between using RAISERROR and ROLLBACK TRANSACTION both in sp ?</p>
  
  <pre><code>ALTER PROCEDURE [dbo].[Cyrex_ChangeUsername]
  
  @oldName nvarchar(128),
  @newName nvarchar(128)
  
  AS
  
  declare @error_var int, @rowcount_var int
  declare @newNameCount int
  
  begin transaction
  
   select @newNameCount = count(*)
    from Users
    where Username = ltrim(rtrim(@newName))
   if @newNameCount &gt; 0 
   begin
    RAISERROR('Username already exists. @newName=%s', 10, 1, @newName) 
    ROLLBACK TRANSACTION
    RETURN
   end
  
   update Users
   set Username = ltrim(rtrim(@newName))
   where Username = ltrim(rtrim(@oldName))
  
   SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
   IF @rowcount_var &lt;&gt; 1 OR @error_var &lt;&gt; 0
   BEGIN
    RAISERROR('Could not Update User.Username. @oldName=%s', 10, 1, @oldName) 
    ROLLBACK TRANSACTION
    RETURN
   END
  
  
   update aspnet_Users
   set 
    Username = @newName,
    LoweredUserName = LOWER(@newName)
   where LoweredUserName = LOWER(@oldName)
  
   SELECT @error_var = @@ERROR, @rowcount_var = @@ROWCOUNT
   IF @rowcount_var &lt;&gt; 1 OR @error_var &lt;&gt; 0
   BEGIN
    RAISERROR('Could not Update aspnet_Users.Username. @oldName=%s', 10, 1, @oldName) 
    ROLLBACK TRANSACTION
    RETURN
   END
  
  Commit transaction
  </code></pre>