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.

Can't you use one `SELECT` statement with 2 conditions in the `WHERE` clause since you're checking the same table? — Ilyes, Jul 02 '19 at 18:16
What about inserting the errors messages into a temporary table and selecting form the temporary table after `IF @errorCOUNT > 0` ? — Raymond Nijland, Jul 02 '19 at 18:18
@Sami In that case I need to check for existence of all my records first and then insert them if no error and your answer is probably no as my conditions are more complex than which I wrote in this example, if I understand your comment. — Pouria Moosavi, Jul 02 '19 at 18:25
@RaymondNijland I thought about it, but as I said in my question it is not actually beautiful to add one temp table to hole my big database and make every thing complex for one query. — Pouria Moosavi, Jul 02 '19 at 18:27
Another option is to have an xml / nvarchar(max) variable and add elements / messages to it. — Serg, Jul 02 '19 at 18:31
Good solution depends on the real type of conditions to be checked. Can you share real code? — Serg, Jul 02 '19 at 18:32
Ok yes i think i didn't read that the first time now i see.. Also this approach i notice now can be prone for possible race condition(s) .. What happens if two clients execute `IF EXISTS (SELECT * FROM Categories WHERE CategoryName = 'myCat1')` at the same time and `CategoryName = 'myCat1'` does not exists you will end up with with two records inserted with `CategoryName = 'myCat1'` .. And it will stack more clients mean more records added.. — Raymond Nijland, Jul 02 '19 at 18:37
@Sami, I though about your solution more, maybe it is possible, I will try it next time. thank you. @Serg, Also your solution was new and interesting to me , so I may try it too. unfortunately it is not possible to share real code now as I have no access from my home :). @FLICKER, your solution was very new for me as I didn't know I can return `json` from `sql` query. @RaymondNijland, this part is administrative part of project, so it is too too rare that this happens. Thank you all. I think one of these solution will solve it for me. — Pouria Moosavi, Jul 02 '19 at 18:47

Ilyes · Answer 1 · 2019-07-02T19:06:43.697

I don't understand what you're really want to do there, but here is a tip using MERGE statement and OUTPUT clause maybe it's what you're after

DECLARE @T TABLE(CategoryName VARCHAR(45));

MERGE INTO T
USING (VALUES('MyCat1'), ('MyCat2')) TT(CategoryName)
ON T.CategoryName = TT.CategoryName -- Or <> instead of =
WHEN NOT MATCHED THEN
INSERT VALUES(TT.CategoryName)
OUTPUT TT.CategoryName INTO @T;

SELECT CASE WHEN CategoryName = 'MyCat1'
            THEN 'This is error for is = 4'
            WHEN CategoryName = 'MyCat2'
            THEN 'This is error for is = 5' 
       END Res
FROM @T;

Also, I don't think you need to the @ErrorCount variable, since you already have @@ROWCOUNT which you can use it instead.

Here is a db<>fiddle where you can see how it's working.

SammuelMiranda · Accepted Answer · 2019-07-03T13:11:19.477

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

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

2 Answers2