Lynn wrote "You really don't get it, do you"

In the UK this would be considered a provocative and rude statement. I will assume cultural differences apply here.

Having used RBDMS since...

Lynn wrote "This means it HAS TO do an implicit conversion of the nvarchar value 'AB' to INT in order to complete the test and fails since 'AB' can not...

SELECT Job_Bag

FROM Job_Bag

WHERE

Job_Bag.Description_Reference = 1609

AND Job_Bag.Cust_Order_No = '70255'

* A row exists which matches the WHERE cluase conditions

* The row with value 'AB' in column Description_Reference has value '70255'...

AB70255

August 2012

Query results using copy paste from SQL SMS results pane

I don't understand.

I described EXACTLY the results.

Not sure how else to post them?

I

Hi Lynn

Sorry for delay in replying - beautiful sunny Saturday here in the UK

The result of the "OR" code is that a resultset containg all, and only, the rows with...

As I mentioned before there was already the value "AB" in a row in the table which cannot be converted to a string.

So one might expect the code to fail...

" There are also implicit conversions in .net"

Assuming you don't mean VB.net Option Explicit, or the Implicit keyword.

And ingoring the VAR debate.....

Can you give some examples of implicit conversions...

The problem with the SP code is indeed the reliance on SQL implicit conversion. Easily fixed. Easily undersood.

The interesting issue is why, like many defects, it only emerged after the...

Thanks everyone.

Putting the peices together I think the answer is this:

The fragment of the SP causing the error is this:

SELECT Job_Bag

FROM Job_Bag

WHERE Job_Bag.Description_Reference = @OrderID

ANDJob_Bag.Cust_Order_No = @OrderReference

*...

@SSCertifiable

There is a consistency within SQL issue here which I don't understand

Why did the conversion NEVER fail with the value 'AB' is in the column?

This value has been there since...

Thanks to all.

BUT - the issue is not conversion per se. I don't need any more info on coercion, casting, conversion etc!

The issue now is - why the code has...

OK

When SQL compares column values with a variable value where the values are diferent data types.

There are 2 posibel methods:

1 Coerce each column value as it is read row by...

Yes - I understand conversion and casting

I wonder if you would re-read my previous post? To confirm what is in it.

I had thought that SQL coerced the @OrderID int value to a varchar to do the comparison

You are suggesting that SQL does the coercion the other way - coerces all...