CODEWITHSUNDEEP
sqlsql-serversql-server-2008sql-server-2012
yohannist
yohannist

Reputation: 4204

SQL if statement debugging

I have the following two queries, The first one works as you would expect. The second raises an exception saying invalid column name ISDELETED. BUT I've added the if else structure precisely to avoid that error, what am i doing wrong On the second query

IF COL_LENGTH('vwAs', 'IsActive') IS NOT NULL  
select  1  
ELSE IF COL_LENGTH('vwABCs', 'IsDeleted') IS NOT NULL
  select 0   
ELSE SELECT -1

And

IF COL_LENGTH('vwAs', 'IsActive') IS NOT NULL  
select   Count(*) [vwB] from [vwAs] WHERE ISACTIVE = 1 
ELSE IF COL_LENGTH('vwABCs', 'IsDeleted') IS NOT NULL
  select Count(*) [vwABCsActive] from [vwABCs] WHERE ISDELETED = 0      
ELSE SELECT -1

Upvotes: 2

Views: 140

Answers (1)

Robert
Robert

Reputation: 25763

Before run the query DB Engine try to validate your code, but not validate your logic. If your table hasn't ISDELETED column DB shows you an error. So you have to hide from DB Engine your wrong code as dynamic sql. Dynamic sql will not be validate.

IF COL_LENGTH('vwAs', 'IsActive') IS NOT NULL  
select   Count(*) [vwB] from [vwAs] WHERE ISACTIVE = 1 
ELSE IF COL_LENGTH('vwABCs', 'IsDeleted') IS NOT NULL
  exec('select Count(*) [vwABCsActive] from [vwABCs] WHERE ISDELETED = 0')
ELSE SELECT -1

Upvotes: 4

Related Questions