Sql Server datatype values issue

[Praveen07]

Hi,

I have come across a strange scenario while executing a query. I was surprised by the way Sql Server was executing the query.

Quote:

SELECT TOP 10 MR.MerchantContactFirstName + ' ' + MR.MerchantContactLastName AS MerchantName, 
AISLTrans.DeviceID,AISLTrans.TransactionID, AISLTrans.CardNumber, AISLTrans.TransactionDateTime, 
AISLTrans.TransactionStatus, AISTRNSTYPE.TransactionType AS TransactionRequestType,AISLTrans.TransactionAmount,
(AISLTrans.BasePointEarn+AISLTrans.BonusPointEarn)AS 'Point Awarded'
FROM AISLoyaltyTransactions AISLTrans INNER JOIN 
MerchantRegistration MR 
ON AISLTrans.MerchantID = MR.MerchantID INNER JOIN 
AISTransactionTypes AISTRNSTYPE 
ON AISLTrans.ProcessingCode = AISTRNSTYPE.ProcessingCode AND 
AISLTrans.TransactionType = AISTRNSTYPE.MessageType WHERE [B]AISLTrans.TransactionType = 0200 [/B]
AND AISLTrans.ProcessingCode = 071000 ORDER BY AISLTrans.TransactionDateTime DESC

In above query the field ISLTrans,Transa ctionType works fine for all the values except 0200 it accepts all interger values though the field type is of varchar.

But when I give the value as 0200 it throws exception saying cannot convert varchar to int.

I am looking for why the query is still executing though I am providing integer value instead of varchar and if it is executing why its not accepting all the integer values which I provide.

Thanks,
Praveen Nelge

[Rabbit]

SQL Server will try to implicitly convert data types to match. You should not rely on this in your queries. You should explicitly make sure that the data types match, otherwise you can run into issues as you are now.

Failure to convert the data type from char to int can come about a few different ways. It could be an overflow issue, or an invalid character issue, or a decimal, etc. There's no way to know without looking at the actual data it is trying to convert.