Sql server datatype values issue

Question

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 AISLTrans.TransactionType = 0200
AND AISLTrans.ProcessingCode = 071000 ORDER BY AISLTrans.TransactionDateTime DESC

In above query the field ISLTrans,TransactionType 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

Praveen N · Answer

Hi Ramesh, Thanks for your reply, actually i am not looking for solution, infact i am looking for the reason why its not accepting all integer values. Thanks

Jignesh Trivedi · Answer

Hi, Here Implicit conversions (Those are invisible to the user) are done.... SQL Server automatically converts the data from one data type to another when required. if a smallint is compared to an int, the smallint is implicitly converted to int before the comparison proceeds. so here also SQL server trying to convert TransactionType type to interger. but If SQL server fail to cast it value to interger than it raise the error.... for exaple try following code declare @t varchar(50) set @t = '0898' If(@t = 1) print 'True' else print 'False' It return "False", when comparision is happend, SQL server trying to cast the value of @t to interger... now try following code declare @t varchar(50) set @t = '08a8' If(@t = 1) print 'True' else print 'False' so at this time SQL server return error "Conversion failed when converting the varchar value '08a8' to data type int."

Ramesh Maruthi · Answer

Hello Praveen, Can you put that 0200 in a single quotes '0200' and give a try once :)

Ramesh Maruthi · Answer

ok Praveen , sorry my fault

Praveen N · Answer

Hi Ramesh, Thanks for your reply, I am not looking for solution I am specially looking for strange behavior of sql Server why isn't it accepting all integer values ? Regards, Praveen Nelge

Sql server datatype values issue

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (5)