How to union multiple select statements while they are not near together?

Question

I have an sql query which check for existence of some records, if those records exist rise error for them otherwise insert them to database. In my query as I need to return error messages for every record, I need to select some custom texts, problem is that they are showing as separate tables, not in one table, which I want (as I am calling this query from nodejs app and it returns an array for me so it only returns first table (error message) for me).

I searched and reach these two options:
1- Use UNION (which is not solving my case)
2- Insert all records in another table and then get all it's record (which isn't beautiful! :) )

DECLARE @errorCOUNT int
SET @errorCOUNT = 0
BEGIN TRANSACTION [Tran1]
IF EXISTS (SELECT * FROM Categories WHERE CategoryName = 'myCat1')
BEGIN
    SELECT 'This is error for is = 4' As err
    SET @errorCOUNT = @errorCOUNT + 1
END
ELSE
BEGIN
    INSERT INTO Categories VALUES ('myCat1')
END
----------------------------
IF EXISTS (SELECT * FROM Categories WHERE CategoryName = 'myCat2')
BEGIN
    SELECT 'This is error for is = 5' AS err
    SET @errorCOUNT = @errorCOUNT + 1
END
ELSE
BEGIN
    INSERT INTO Categories VALUES ('myCat2')
END
----------------------------
IF @errorCOUNT > 0
BEGIN
    ROLLBACK TRANSACTION [Tran1]
END
ELSE 
BEGIN
    COMMIT TRANSACTION [Tran1]
END

As I mentioned I want all these select statements to be shown in one table so they return to my server as one array.
I just think it is good to mention that my query completes in a loop, so it may have different amount of IF...ELSE (between --- lines).

I hope I was clear. Thanks in advance.

Answer 1

Try this one, would work:

BEGIN TRANSACTION [Tran1]
DECLARE @err AS TABLE ( msg NVARCHAR(MAX) NOT NULL )
DECLARE @errorCOUNT AS INT = 0
IF EXISTS (SELECT * FROM Categories WHERE CategoryName = 'myCat1')
BEGIN
  INSERT INTO @err (msg) VALUES ('This is error for is = 4')
  SET @errorCOUNT = @errorCOUNT + 1
END
ELSE
BEGIN
  INSERT INTO Categories VALUES ('myCat1')
END
IF EXISTS (SELECT * FROM Categories WHERE CategoryName = 'myCat2')
BEGIN
  INSERT INTO @err (msg) VALUES ('This is error for is = 5')
  SET @errorCOUNT = @errorCOUNT + 1
END
ELSE
BEGIN
  INSERT INTO Categories VALUES ('myCat2')
END
IF @errorCOUNT > 0
BEGIN
  SELECT * FROM @err
  ROLLBACK TRANSACTION [Tran1]
END
ELSE 
BEGIN
  COMMIT TRANSACTION [Tran1]
END