2

I've an SQL statment like this

declare @keyword varchar(100)='ndee'
select ISNUMERIC(@keyword)

select * from teststaken where ( 
   (firstname like '%' + @keyword + '%') or  
   (lastname like '%' + @keyword + '%') or  
    TestTakerEmail like '%' + @keyword + '%' or 
   (ISNUMERIC(@keyword)=1 and TestsTakenID = @keyword)
)

Its only a searching query for selecting rows based on a keyword given against different columns. But the teststakenID column is integer. So it works fine if we avoid the last comparison when a string is given. If a string or a word is given it throws an error

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

But what I tried to accomplish was checking the keyword is integer type or not and only integer type then compare with the teststakenid column. Please help me to find what I did wrong

Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
Sandeep Thomas
  • 4,303
  • 14
  • 61
  • 132

1 Answers1

3

Try this:

declare @keyword varchar(100)='ndee'

select * 
from teststaken 
where firstname like '%' + @keyword + '%' 
or lastname like '%' + @keyword + '%' 
or TestTakerEmail like '%' + @keyword + '%' 
or cast(TestsTakenID as varchar(100)) = @keyword

Explanation: You get this exception since Sql server tries to evaluate every condition on your where clause, even if it's after an and keyword (unlike c# when using && or vb.net when using AndAlso).
This results in an attempt to evaluate the int value of TestsTakenID to the varchar value of @Keyword.

Since any integer value can be converted to a varchar value providing it's length is sufficient, you can simply cast the TestsTakenID to varchar(100) (since it's the length of your parameter anyway). once we do that, there is really no need for the ISNUMERIC test as paul rightfully wrote in the comments.
Thanks to paul and LittleBooby tables for the comments, I've learned something new today :-).

Community
  • 1
  • 1
Zohar Peled
  • 79,642
  • 10
  • 69
  • 121
  • @LittleBobbyTables `(isnumeric(@keyword) = 1 and cast(@keyword as int) = TestsTakenID)` works just fine for me – paul May 18 '15 at 14:43
  • @paul - well what the crap. based on [this](http://stackoverflow.com/questions/789231/is-the-sql-where-clause-short-circuit-evaluated), I was always under the impression SQL Server didn't do short-circuiting, outside of `CASE` expressions, but I tested the SQL example, and retract my previous statement. Very surprised by this. Either something has changed in SQL Server, or I wouldn't expect this to be reliably consistent. – LittleBobbyTables - Au Revoir May 18 '15 at 14:46
  • @Zohar - that's what I get for doubting myself. See the link in my response to Paul; short-circuiting can't always be guaranteed in SQL Server, and you're seeing this in the SQL Fiddle. Also see [here](http://stackoverflow.com/questions/381224/sql-server-query-short-circuiting) for a SQL Server-specific question and answer. – LittleBobbyTables - Au Revoir May 18 '15 at 14:55
  • Well, in that case, I should probably rollback my edits :-) btw, I love your username. I'm always using `Exploits of a Mom` as a link to explain sql injection attacks. – Zohar Peled May 18 '15 at 14:55
  • @ZoharPeled - eeyup :) – LittleBobbyTables - Au Revoir May 18 '15 at 14:57