i'm trying set sql test queries document whether tests pass or fail in sql table. these test queries run on several separate databases. here example using basic query (inside if statement):
declare @dbname nvarchar(200); declare @query nvarchar(max); declare db_cursor cursor select name sys.databases name '%jmptipr%' open db_cursor fetch next db_cursor @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 [' +@dbname+ '].dbo.table1 column1 = ''file name'' select @version = attributedata [' +@dbname+ '].dbo.table1 column1 = ''version number'' select @testid = coalesce(max(testnum), 0) [database1].dbo.table2; select @repid = repnum [database1].dbo.table2 date = (select max(date) [database1].dbo.table2) if ( select count(*) [' +@dbname+ '].dbo.table1 [' +@dbname+ '].dbo.table1.column1 ''%execution%'' ) not null set @teststatus = ''test passed'' else set @teststatus = ''test failed'' insert [database1].dbo.table3 (filename, version, result, date, testnum, repnum) values (@name, @version, @teststatus, getdate(), @testid, @repid)' nvarchar(max)) execute (@query) fetch next db_cursor @dbname end close db_cursor deallocate db_cursor; the problem have several more complicated queries want same thing with. have expected result should return if went according plan , expected result if broke. here's example of 1 of more complex queries want include: (worth noting, don't want answer question 'fixing' query. have many put here fix , i'd rather learn how myself.)
select table1.column1, table2.column2, table3.column3, table3.column4, table3.column5, table3.column6, table3.column7 table2 inner join table3 on table3.column8 = table2.id inner join table1 on table2.column9 = table1.id (table3.column5 = -1 , table3.column7 > 0) or (table3.column5 = -1 , table3.column6 > 0) or (table3.column5 > table3.column6 , table3.column6 > 0) or (table3.column6 > table3.column7 , table3.column7 > 0) when slap in query if statement (and change if condition is not null), error message:
msg 116, level 16, state 1, line 24 1 expression can specified in select list when subquery not introduced exists. msg 116, level 16, state 1, line 24 1 expression can specified in select list when subquery not introduced exists. to me seems ultimate issue don't have advanced enough understanding of sql make these work.
edit: see several comments saying because didn't show code isn't working, can't fix it. assuming mean example code provided isn't specific enough.
however, missing point of question. don't want fix particular code. question original if/else code not being able handle certain, more complex sql queries (like ones bring full table).
to perhaps give more context example code did provide, query either bring nothing (the "test passed" condition) or bring table multiple rows , columns (the "test failed" condition). asking how take original query have works simple queries , adapt queries not able "counted" or other simple functions.
since marc doesn't seem interested in following through, here answer question.
the "complexity" of query has no bearing on issue. got error 1 reason, , have gotten same error simple query well.
this query works (as noted):
if ( select count(*) [' +@dbname+ '].dbo.table1 [' +@dbname+ '].dbo.table1.column1 ''%execution%'' ) not null ... the reason query works because subquery in if() returns single value. count. 1 row, 1 column. 1 value can tested see if null, there no problem.
this query not work:
if ( select column1, column2 [' +@dbname+ '].dbo.table1 [' +@dbname+ '].dbo.table1.column1 ''%execution%'' ) not null ... the reason not work subquery in if() returns more 1 column, results of query cannot compared null. maybe in mind there way think might done (either column null, both columns null, etc.), assure cannot. sql engine not know how compare resultset (multiple columns) null. not way sql server works. can compare single, scalar value null.
that why got error:
only 1 expression can specified in select list when subquery not introduced exists.
your "complex" query has more 1 column (expression) in select list. , reason. no matter how complex query is, if returns 1 column, can use in if() statement no problem.
looking @ edit, if need test whether complex query returns rows or not, can replace column list count(*) did in query works.
