SQL Transaction Duplicate Entry

You don't need a transaction unless you wanna enforce an isolation level, or if you have more than one Update/Insert/Delete statement, and you need to roll them all back if an error happens, or commit them all if everything is successful,

in your sample code, you have an insert statement that won't even get to execute, so you have nothing to rollback

DECLARE @InsertedRows INT = 0

INSERT INTO Forums(AddedBy, AddedDate, Title, Description, ParentID, 
    Moderated, ImageUrl, UpdatedBy, UpdatedDate, Active, Importance)
SELECT @AddedBy, @AddedDate, @Title, null, null, 'False', null, null, null, 'True', 0
WHERE NOT EXISTS (SELECT * FROM Forums WHER Title = @Title)


SET @InsertedRows = @@ROWCOUNT

IF @InsertedRows = 1 
  RETURN 1
ELSE
  RETURN -1

BEGIN TRANSACTION

    IF EXISTS (SELECT * FROM Forums WHERE Title = @Title) 
    BEGIN
        RAISERROR ('Duplicate Entry', 16, 1)
    END
    ELSE
    BEGIN   
          INSERT INTO Forums(AddedBy, AddedDate, Title, Description, ParentID, 
              Moderated, ImageUrl, UpdatedBy, UpdatedDate, Active, Importance)
          VALUES(@AddedBy, @AddedDate, @Title, null, null, False, null, null, null, True, 0)

          COMMIT TRANSACTION

        RETURN @@ROWCOUNT
    END

    IF @@ERROR <> 0
    BEGIN
        ROLLBACK TRANSACTION
        RETURN @@ERROR
    END

You don't really need the transaction. A single-row insert is either going to succeed or fail (and @@ROWCOUNT is only every going to return 1 in this case). And if you have a unique constraint on the title column (which you should have, apparently), you don't need to check first, either, though it may be a bit more efficient to do so (I'll have to perform some tests to confirm, but I'm pretty sure the error mechanism is pretty costly). Anyway here is a version with error handling in case something should go wrong other than a constraint violation on the title:

IF EXISTS (SELECT 1 FROM dbo.Forums WHERE Title = @Title)
BEGIN
    RAISERROR('Duplicate entry', 16, 1);
    RETURN 2627; -- key constraint violation
END
ELSE
BEGIN
    BEGIN TRY
        INSERT dbo.Forums
        (
          AddedBy, AddedDate, Title, 
          Description, ParentID, 
          Moderated, ImageUrl, UpdatedBy, 
          UpdatedDate, Active, Importance
        )
        SELECT
          @AddedBy,
          @AddedDate,
          @Title, 
          NULL, NULL, 
          'False', -- did you mean 0?
          NULL, NULL, NULL,
          'True', -- did you mean 1?
          0;

        RETURN 1;
    END TRY
    BEGIN CATCH
        DECLARE @msg NVARCHAR(4000) = 'Insert failed ' + ERROR_MESSAGE();
        RAISERROR(@msg, 16, 1);
        RETURN ERROR_NUMBER();
    END CATCH
END