Sql Server Datatype values issue

Question

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.

Make your life easier and fix your code. WHERE AISLTrans.TransactionType = '0200'. Don't rely on implicit conversions. It is terrible for performance and you run into these types of random issues. The reason this happens sometimes is because the execution plan may vary slightly and it can change the order that the predicates are resolved internally. You most likely have some rows in that table where TransactionType is something other than an integer. — Sean Lange
– Sean Lange, Commented Jul 17, 2014 at 14:36

Ondipuli · Accepted Answer · 2014-07-17 14:41:11Z

1

Implement a standard way while giving input.

If the AISLTrans.TransactionType filed type is integer, Use these code

AISLTrans.TransactionType = CAST(0200 AS INT)

else use these code

AISLTrans.TransactionType = '0200'

answered Jul 17, 2014 at 14:41

Ondipuli

4683 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Sql Server Datatype values issue

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related