Hello,

Running out of ideas how to fix this case. I am getting an error: Error converting data type varchar to numeric.

CASE
WHEN sopHdr.SOPTYPE = 4
THEN CONVERT(int,(sopHdr.DOCAMNT - inv.TaxTotal) - CONVERT(int, inv.CostTotal)) * - 1 * CONVERT(int,proj.UserText1) / 100.0
ELSE CONVERT(int,(sopHdr.DOCAMNT - inv.TaxTotal) - inv.CostTotal) * CONVERT(int,proj.UserText1) / 100.0
END

Please help.

Assuming your first table is named A with a column named MyIntColumn of type int and your second table is named B with a column named MyNVarcharColumn of type nvarchar, you could do

From A
Inner Join B On A,MyIntColumn = Try_Cast(B.MyNVarcharColumn As int) 

Tom

Lining up your arguments with the parameters:

'insert'          -> @type varchar(50)
'dsad'            -> @Customer_ID int    
'[email protected]'   -> @Customer_Name varchar(50)
'pass'            -> @Email varchar(50)
'9999900000'      -> @Password varchar(50)
                  -> @Mobile varchar(50) = null

...you can see that 'dsad' has no way of being understood as an int. Maybe there's a problem elsewhere as well, but at the very least, the stored procedure is not being called correctly.

Update:

If your intent was to omit some arguments that aren't relevant in a certain case, you have to use named parameters; otherwise, arguments can only be applied in the same order the parameters appear:

exec customer_registrations 
  'insert', 
  @Customer_Name = 'dsad', 
  @Email         = '[email protected]', 
  @Password      = 'pass', 
  @Mobile        = '9999900000';

SQL Server 2012 and Later

Just use Try_Convert instead:

TRY_CONVERT takes the value passed to it and tries to convert it to the specified data_type. If the cast succeeds, TRY_CONVERT returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CONVERT fails with an error.

Read more about Try_Convert.

SQL Server 2008 and Earlier

The traditional way of handling this is by guarding every expression with a case statement so that no matter when it is evaluated, it will not create an error, even if it logically seems that the CASE statement should not be needed. Something like this:

SELECT
   Account_Code =
      Convert(
         bigint, -- only gives up to 18 digits, so use decimal(20, 0) if you must
         CASE
         WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
         ELSE X.Account_Code
         END
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      CASE
      WHEN X.Account_Code LIKE '%[^0-9]%' THEN NULL
      ELSE X.Account_Code
      END
   ) BETWEEN 503100 AND 503205

However, I like using strategies such as this with SQL Server 2005 and up:

SELECT
   Account_Code = Convert(bigint, X.Account_Code),
   A.Descr
FROM
   dbo.Account A
   OUTER APPLY (
      SELECT A.Account_Code WHERE A.Account_Code NOT LIKE '%[^0-9]%'
   ) X
WHERE
   Convert(bigint, X.Account_Code) BETWEEN 503100 AND 503205

What this does is strategically switch the Account_Code values to NULL inside of the X table when they are not numeric. I initially used CROSS APPLY but as Mikael Eriksson so aptly pointed out, this resulted in the same error because the query parser ran into the exact same problem of optimizing away my attempt to force the expression order (predicate pushdown defeated it). By switching to OUTER APPLY it changed the actual meaning of the operation so that X.Account_Code could contain NULL values within the outer query, thus requiring proper evaluation order.

You may be interested to read Erland Sommarskog's Microsoft Connect request about this evaluation order issue. He in fact calls it a bug.

There are additional issues here but I can't address them now.

P.S. I had a brainstorm today. An alternate to the "traditional way" that I suggested is a SELECT expression with an outer reference, which also works in SQL Server 2000. (I've noticed that since learning CROSS/OUTER APPLY I've improved my query capability with older SQL Server versions, too--as I am getting more versatile with the "outer reference" capabilities of SELECT, ON, and WHERE clauses!)

SELECT
   Account_Code =
      Convert(
         bigint,
         (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
      ),
   A.Descr
FROM dbo.Account A
WHERE
   Convert(
      bigint,
      (SELECT A.AccountCode WHERE A.Account_Code NOT LIKE '%[^0-9]%')
   ) BETWEEN 503100 AND 503205

It's a lot shorter than the CASE statement.

In order to avoid such error you could use CASE + ISNUMERIC to handle scenarios when you cannot convert to int.
Change

CONVERT(INT, CONVERT(VARCHAR(12), a.value))

To

CONVERT(INT,
        CASE
        WHEN IsNumeric(CONVERT(VARCHAR(12), a.value)) = 1 THEN CONVERT(VARCHAR(12),a.value)
        ELSE 0 END) 

Basically this is saying if you cannot convert me to int assign value of 0 (in my example)

Alternatively you can look at this article about creating a custom function that will check if a.value is number: http://www.tek-tips.com/faqs.cfm?fid=6423

This is likely (we can't see enough of the code or data to be sure) a common issue with database order of operations. You have varchar columns backing f.ID and i.ID that have mixed data in them: some values can convert to int, but some cannot. You are then using a WHERE clause or JOIN condition to limit the query to only include values that do convert safely to int.

The problem is databases do not and will NEVER promise to use the order of operations you expect. There are many cases where the database will deem it more efficient to do the data projections (ie: CONVERT() calls) earlier, and the filtering operations later. And when this happens there is very little you can do to convince the database it needs to change this.

Instead, use the TRY_CONVERT() function that will give a NULL result for the invalid rows without failing the whole query. Or, even better, define the schema in a way that doesn't mix data types.

It's because the first parameter of the REPLICATE function is a VARCHAR, not int. Please see the documentation here.

Instead of doing REPLICATE(0, 4) you'll want to do REPLICATE('0', 4) etc.

The error itself can be a little confusing when it says the word convert, because it doesn't necessarily mean the CONVERT function, rather it's trying to do implicit conversion of the INT value 0 to a VARCHAR value since that is the data type of the parameter for the REPLICATE function. This error can occur for any function that expects a certain data type for its parameter but is passed in a value that's a different data type that is unable to be implicitly converted.

An example of implicit conversion that would work, is if a function had a parameter that was of type BIGINT and you passed in an INT value like 13 instead.

The problem appears to be that your stored procedure expects 8 parameters:

stp_customers_insert(@customerid int, @name varchar(50), @phone varchar(50),
                     @doorno varchar(50), @streetname varchar(50), @city varchar(50),
                     @state varchar(50), @zip int)

but you are only passing 7 parameters when you actually call the proc:

exec stp_customers_insert 'ram','674673932','122','5th cross','trichy','tamilnadu',620001

If you don't know or don't want to perform the duplicate check on the CustomerID, then you could slightly modify your call to just pass NULL:

exec stp_customers_insert NULL, 'ram','674673932','122','5th cross','trichy','tamilnadu',620001

As an aside, if the proc is not even inserting the CustomerID, and this field is auto increment, then I don't see the point of passing it. Instead, you might want to consider using a unique constraint to achieve the same.

You can't convert strings to integers if they are not made entirely from numbers.

So, if you try to convert aaa to an int, this will fail. If you try to convert the varchar 123 to an int, it will work.

OK. I finally created a view that works:

SELECT TOP (100) PERCENT id, CAST(CASE WHEN IsNumeric(MyCol) = 1 THEN MyCol ELSE NULL END AS bigint) AS MyCol
FROM         dbo.MyTable
WHERE     (MyCol NOT LIKE '%[^0-9]%')

Thanks to AdaTheDev and CodeByMoonlight. I used your two answers to get to this. (Thanks to the other repliers too of course)

Now when I do joins with other bigint cols or do something like 'SELECT * FROM MyView where mycol=1' it returns the correct result with no errors. My guess is that the CAST in the query itself causes the query optimizer to not look at the original table as Christian Hayter said may be going on with the other views

You'd need to determine exactly what character was in the original string. Hopefully you have a copy of the original somewhere. I'm guessing it wasn't actually a full stop even if it displayed as one. This query may help

declare @s table
(
    col varchar(20) not null
);

insert @s(col) values ('2010'), ('2010.   ');

;with Numbers as
(
    select top(20)
        rn = ROW_NUMBER() over (order by (select 1))
    from sys.objects as o
)
select
    s.col,
    n.rn,
    SUBSTRING(s.col, n.rn, 1),
    ASCII(SUBSTRING(s.col, n.rn, 1)),
    UNICODE(SUBSTRING(s.col, n.rn, 1))
from @s as s
cross apply Numbers as n
where n.rn < = DATALENGTH(s.col)
and s.col like '%2010%'
order by
    s.col,
    n.rn;

The CTE is just a quick numbers table statically sized to the column's declared width. It will return one row per possible character position in the input data.

The main query uses the CTE's output as an ordinal index to extract one character at a time.

It looks like you may have missed a field in your line to execute the stored procedure. I quickly copied the stored proc input values into a spreadsheet followed by the values in your test line that throws the error. Have a look, it looks like you're missing perhaps the Day value? After that is entered it should line everything up correctly. I marked up the first line that after a quick glance looks like it is causing the error (others lower will too though, until the missing value is fixed).

Something, somewhere is doing an implicit conversion. Since int has a higher precedence than varchar the string "www.com" is being cast to int and failing.

First try a simple select * from view_a. Does that succeed? If it does it suggest table1 and table2 have compatible data types in their corresponding columns.

Second check the types assigned to columns of #TempTable. Are they what is expected?

Then check select * from view_A where TRY_CAST(View_A.ColumnA AS INT) is null. Are any rows returned? If so the view's column A is not what you think investigate that.

TRY_CAST returns NULL if the cast fails. I would expect rows that do not cast correctly to be omitted from the join in your second "I've tried".

If there's still no resolution create copies of tables 1, 2 and 3 with just column A. Do the views and queries work then? If so add back further columns, perhaps a few at a time if there are many, and repeat until the error is thrown.