title: Using complex SQL queries in conditional if/else statements
body: <p>I'm trying to set up some SQL test queries to document whether the tests pass or fail in a SQL table. These test queries are run on several separate databases. Here is an example using a basic query (inside the IF statement):</p>
  
  <pre><code>DECLARE @dbname NVARCHAR(200);
  DECLARE @query NVARCHAR(MAX);
  
  DECLARE db_cursor CURSOR FOR
  SELECT name FROM sys.databases
  WHERE name LIKE '%JMPTIPR%'
  
  OPEN db_cursor
  FETCH NEXT FROM db_cursor INTO @dbname
  
  WHILE @@FETCH_STATUS = 0
  
  BEGIN
  
  SET @query = CAST('
  DECLARE @testStatus NVARCHAR(MAX);
  DECLARE @name NVARCHAR(MAX);
  DECLARE @version NCHAR(10);
  DECLARE @testid INT;
  DECLARE @repid INT;
  
  SELECT @name = Column1 from [' +@dbname+ '].dbo.Table1
  where Column1 = ''File Name''
  SELECT @version = attributedata from [' +@dbname+ '].dbo.Table1 
  where Column1 = ''Version Number''
  SELECT @testid = COALESCE(MAX(TestNum), 0) FROM [Database1].dbo.Table2;
  SELECT @repid = RepNum FROM [Database1].dbo.Table2 
  WHERE Date = (SELECT MAX(Date) FROM [Database1].dbo.Table2)
  
  IF (
      SELECT COUNT(*)
      FROM [' +@dbname+ '].dbo.Table1
      WHERE [' +@dbname+ '].dbo.Table1.Column1 LIKE ''%execution%''
  ) IS NOT NULL
      SET @testStatus = ''Test Passed''
  ELSE
      SET @testStatus = ''Test Failed''
  
  INSERT INTO [Database1].dbo.Table3 (FileName, Version, Result, Date, TestNum, RepNum)
  VALUES (@name, @version, @testStatus, GETDATE(), @testid, @repid)'
  AS NVARCHAR(MAX))
  
  EXECUTE (@query)
  
  FETCH NEXT FROM db_cursor INTO @dbname
  
  END
  CLOSE db_cursor
  DEALLOCATE db_cursor;
  </code></pre>
  
  <p>The problem is that I have several much more complicated queries that I want to do the same thing with. I have an expected result that should return if everything went according to plan and an expected result if something broke. So here's an example of one of the more complex queries that I want to include:
  (Worth noting, that I don't want anyone to simply answer this question by 'fixing' the query. I have too many to put up here for someone to fix and I'd rather learn how to do it myself.)</p>
  
  <pre><code>SELECT Table1.Column1, Table2.Column2, Table3.Column3,
    Table3.Column4, Table3.Column5, Table3.Column6,
    Table3.Column7
  FROM Table2 INNER JOIN
    Table3 ON Table3.Column8 = Table2.id INNER JOIN
    Table1 ON Table2.Column9 = Table1.id
  WHERE (Table3.Column5 = -1 AND Table3.Column7 &gt; 0) OR
    (Table3.Column5 = -1 AND Table3.Column6 &gt; 0) OR
    (Table3.Column5 &gt; Table3.Column6 AND Table3.Column6 &gt; 0) OR
    (Table3.Column6 &gt; Table3.Column7 AND Table3.Column7 &gt; 0)
  </code></pre>
  
  <p>When I slap in this query into my if statement (and change the if condition to <code>IS NOT NULL</code>), I get the error message: </p>
  
  <pre><code>Msg 116, Level 16, State 1, Line 24
  Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
  Msg 116, Level 16, State 1, Line 24
  Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
  </code></pre>
  
  <p>To me it seems like the ultimate issue is that I don't have an advanced enough understanding of SQL to make these work.</p>
  
  <p><strong>EDIT:</strong> I see several comments saying that because I didn't show the code that isn't working, you can't fix it. I am assuming that you mean that the example code I provided isn't specific enough. </p>
  
  <p>However, that is missing the point of my question. I don't want you to fix that particular code. My question was about the original If/Else code not being able to handle certain, more complex SQL queries (like ones that bring back a full table). </p>
  
  <p>To perhaps give some more context to the example code that I did provide, it is a query that will either bring back nothing (the "Test Passed" condition) or it will bring back a table with multiple rows and columns (the "Test Failed" condition). I was asking about how to take the original query that I have that works for simple queries and adapt it to queries that will not be able to be simply "Counted" or other simple functions.</p>