How to store different select value to the same variable

Question

Hi,

Is it possible to store different select value to the same variable?

DECLARE @cout int 

SELECT @cout= count(*) FROM Master1 WHERE (...)

SET @cout = SELECT count(*) FROM Master2 WHERE (...)

SET @cout = SELECT count(*) FROM Master3 WHERE (...)

IF(@cout = 0)
 BEGIN
 END

Answer 1

No, what you've got there will replace the value. You can append them as strings though, or add up the numbers. But it's bad practice; better to return a resultset, i.e:

SELECT
    (select count(*) FROM Master1 WHERE (...)) as 'Foo1',
    (SELECT count(*) FROM Master2 WHERE (...)) as 'Foo2',
    (select count(*) FROM Master3 WHERE (...)) as 'Foo3'

Answer 2

How about:

IF EXISTS
(
SELECT 1 FROM Master1 WHERE (...)
UNION ALL
SELECT 1 FROM Master2 WHERE (...)
UNION ALL
SELECT 1 FROM Master3 WHERE (...)
)    
BEGIN
...
END

It should do the trick, and pull out quickly enough...

Answer 3

You want to only run if none of these exist.

You could use the EXISTS keyword.

Something like this.

IF NOT EXISTS SELECT ID FROM Master1 WHERE (...)
  IF NOT EXISTS SELECT ID FROM Master2 WHERE (...)
    IF NOT EXISTS SELECT ID FROM Master3 WHERE (...)
    BEGIN
      ..do stuff...
    END

Note that I don't use count, if count is zero NOT EXISTS is true. Select any column to get the query to run.

Can't test right now, the syntax might look like this:

IF NOT EXISTS (SELECT ID FROM Master1 WHERE (...))
  IF NOT EXISTS (SELECT ID FROM Master2 WHERE (...))
    IF NOT EXISTS (SELECT ID FROM Master3 WHERE (...))
    BEGIN
      ..do stuff...
    END