CODEWITHSUNDEEP
sql-serversql-server-2008
Amarundo
Amarundo

Reputation: 2397

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

This is in SQL Server 2008 R2.

I'm pulling my hair with this one. Follow closely, please.

When I run this, I get 27 rows returned:

select * 
from dbo.[12STD_NO_VISIT] 
where (
         (dbo.fPhoneExists(PhoneNumber1) = 1  
         AND (NextCall1 BETWEEN GETDATE() AND DATEADD(hh, 1, GETDATE())))
      )

And when I run this, I get 21 rows returned (notice the change to PhoneNumber2 and NextCall2):

select * 
from dbo.[12STD_NO_VISIT] 
where (
          (dbo.fPhoneExists(PhoneNumber2) = 1  
           AND (NextCall2 BETWEEN GETDATE() AND DATEADD(hh, 1, GETDATE())))
      )

But, when I run this, 'ORing' the 2 conditions, I get an error:

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

select * 
from dbo.[12STD_NO_VISIT] 
where (
          (dbo.fPhoneExists(PhoneNumber1) = 1  
           AND (NextCall1 BETWEEN GETDATE() AND DATEADD(hh, 1, GETDATE())))
       OR  
          (dbo.fPhoneExists(PhoneNumber2) = 1
           AND (NextCall2 BETWEEN GETDATE() AND  DATEADD(hh, 1, GETDATE())))
      )

But it doesn't just give me the error. It first retrieves 42 rows, displaying that for a split second (Results tab), and then it displays the error (Messages tab).

I can't figure this one out. Any help is very appreciated.

Thanks!

FUNCTION [dbo].[fPhoneExists](@PhoneNumber varchar) 
RETURNS BIT 
WITH EXECUTE AS CALLER 
AS 
BEGIN 
DECLARE @GoodNumber bit 
IF (@PhoneNumber is NULL or @PhoneNumber = 0 or @PhoneNumber = '') 
  SET @GoodNumber = 0; 
ELSE 
  SET @GoodNumber = 1; 
  Return(@GoodNumber); 
END

Upvotes: 2

Views: 18579

Answers (3)

John Dewey
John Dewey

Reputation: 7093

You'll get better performance replacing fnPhoneExists by replacing the function with a CASE:

select * 
from dbo.[12STD_NO_VISIT] 
where (
          (case when ISNULL(PhoneNumber1,'') not in ('0','') then 1 else 0 end=1
           AND (NextCall1 BETWEEN GETDATE() AND DATEADD(hh, 1, GETDATE())))
       OR  
          (case when ISNULL(PhoneNumber2,'') not in ('0','') then 1 else 0 end=1
           AND (NextCall2 BETWEEN GETDATE() AND  DATEADD(hh, 1, GETDATE())))
      )

This is because the optimizer won't optimize the contents of fnPhoneExists.

Upvotes: 1

anon
anon

Reputation:

What are the data types of PhoneNumber1 and PhoneNumber2? What is the definition of dbo.fPhoneExists? I suspect the problem lies there somewhere - or as Lynn suggests maybe there is more to the query than you've shown us (the query would either succeed or fail as a whole; it wouldn't produce 42 rows and then an error).

Now that we see the function, here is a re-write:

ALTER FUNCTION [dbo].[fPhoneExists]
(
  @PhoneNumber VARCHAR -- varchar(what)? This should match definition of column
) 
RETURNS BIT 
WITH EXECUTE AS CALLER 
AS 
BEGIN
  RETURN (SELECT CASE WHEN COALESCE(@PhoneNumber, '') IN ('0', '') THEN 0 ELSE 1 END);
END
GO

There is no reason to store the temporary logic in a variable, also notice that you are returning the variable only in the ELSE condition.

Upvotes: 4

Remus Rusanu
Remus Rusanu

Reputation: 294287

Your dbo.fPhoneExists function contains an implicit cast in the PhoneNumber = 0 expression that, according to the rules of Data Type Peecendence casts the PhoneNumber VARCHAR value to an int. This cast will fail if the value in the string is not a numeric. You are also falling into the fallacy of assuming that boolean operator short circuit is guaranteed in SQL, which is simply not true. SQL is a declarative language and the order of evaluation of boolean operators is not guaranteed.

Upvotes: 3

Related Questions