Earthling Earthling - 3 months ago 6
C# Question

SQL: Running two queries at once and Assigning Variables

I am basically trying to run these two queries:

SELECT * FROM ProductTable;

SELECT CAST(CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS BIT)
FROM UserTable WHERE id = 41;


Both queries work properly. The first one returns me all the data in ProductTable. The second query returns me either
1
or
0
after checking if the row ID 41 exists

Running them together:

SELECT * FROM ProductTable SELECT CAST(CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END AS BIT)
FROM UserTable WHERE id = 41


However, when I run this, the second query does not return any value, this is because I have not set a SQL variable name to it.

How can I set a Variable name to the second query such that I can read that value when reading the SQL response?

Answer

Try like this,

SELECT *
    ,(
        SELECT CAST(CASE 
                    WHEN COUNT(*) > 0
                        THEN 1
                    ELSE 0
                    END AS BIT)
        FROM UserTable
        WHERE id = 41
        ) AS UserCount
FROM ProductTable;