CODEWITHSUNDEEP
sqlsql-server
user7334823
user7334823

Reputation:

delete query is giving error but same condition works in select

delete from T_SEARCH_TABLES
where TABLE_NAME = 'D_VENDOR'
      and SEARCH_ID in(select distinct htvf.Value
                       from T_RESX_VALUES
                            cross apply dbo.func_split(T_RESX_VALUES.[key],'.') as htvf
                       where T_RESX_VALUES.value like '%Invoices - Activity (%'
                              and htvf.Position = '2'
                              and T_RESX_VALUES.[KEY] like 'SearchName.%'
                      )
      and SEARCH_TABLE_ID not in(select SEARCH_TABLE_ID
                                 from T_SEARCH_COLUMNS
                                );

Is giving error:

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'SearchName' to data type int.

But 

select *
from T_SEARCH_TABLES
where TABLE_NAME = 'D_VENDOR'
      and SEARCH_ID in(select distinct htvf.Value
                        from T_RESX_VALUES
                            cross apply dbo.func_split(T_RESX_VALUES.[key],'.') as htvf
                        where T_RESX_VALUES.value like '%Invoices - Activity (%'
                              and htvf.Position = '2'
                              and T_RESX_VALUES.[KEY] like 'SearchName.%'
                       )
      and SEARCH_TABLE_ID not in(select SEARCH_TABLE_ID
                             from T_SEARCH_COLUMNS
                            );

is working fine what is the problem in it ?

Upvotes: 0

Views: 404

Answers (1)

jambonick
jambonick

Reputation: 716

Are you sure that at both DELETE and SELECT statements the first conditions of your inner WHERE CLAUSE 

(T_RESX_VALUES.value like '%Invoices - Activity (%'and htvf.Position = '2')

were met?

Let me explain

I have run these two statements at my DB

select * from customers where cust_id = 0 and Cust_Name = 1

select * from customers where cust_id = 816171 and Cust_Name = 1

The Cust_Name column is of type varchar. My table contains one customer with ID = 816171 and Cust_Name = 'Mike' but no customers with ID = 0.

I got the following results: The first query run ok but returned no rows

The second query returned the following error:

Conversion failed when converting the varchar value 'Mike' to data type int.

It seems that sql-server doesn't try even to commit the conversion if the first condition is not met.

Actually the reason is that SQL doesn't always executes checks of where clauses in same order, the engine itself chooses every time the most optimized order.

You can read here more: http://rusanu.com/2009/09/13/on-sql-server-boolean-operator-short-circuit/

Upvotes: 2

Related Questions