April 23, 2009 at 11:21 pm
Hai Everybody,
I have a doubt.. if one variable is having datatype as varchar in one table and integer in another table and when we are comparing both columns, will there any problem araises?
Please let me know. Because, when I am getting master tables from our head office, those are having all columns as varchar and in transaction table we are using as integer. In stored procedures we are checking the record existing in master tables by comparing both. Please let me know about this.
Thank you all
April 23, 2009 at 11:35 pm
nageswara.dhaveji (4/23/2009)
Hai Everybody,I have a doubt.. if one variable is having datatype as varchar in one table and integer in another table and when we are comparing both columns, will there any problem araises?
Please let me know. Because, when I am getting master tables from our head office, those are having all columns as varchar and in transaction table we are using as integer. In stored procedures we are checking the record existing in master tables by comparing both. Please let me know about this.
Thank you all
use CAST or CONVERT functions in your query, for more info. in SQL BOL.
April 23, 2009 at 11:56 pm
If the varchar column contains integer data implicit conversion will occur and comparision will work. But this is not the best way to do it. You may also get performance issues. You should have the same data types for the same column. How ever if your data contains character data then you will get an error while comparing a varchar column to a integer column.
"Keep Trying"
April 24, 2009 at 12:13 am
this will cause implicit conversions !
Implicit conversions may cause indexes not to be used optimal or not at all.
Even with cast/convert (which are explicit conversions) one of both columns index may not be used to the optimum.
Best is to align your datatypes to the correct datatype. (don't just use (var)char because it can contain all)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 24, 2009 at 12:33 am
Thank you very much for the replies. I want to suggest the person who downloads the master table to make correct data types while downloading. Thank you.
April 24, 2009 at 1:06 am
In such case when Source have different datatypes as compare to the destination, there are alot of comparisons to be made to avoid the conversion errors.
You will have to check the validity of the varchar values with IsNumeric() function before convert / cast. Otherwise, it wil generate error.
Declare @tbl Table (id int, idv varchar(10))
Insert into @tbl
Select 1,'1' Union All
Select 2,'2' Union All
Select 3,'3' Union All
Select 4,'K'
--- Error in Query
Select case when
id = Convert(int,idv)
then 1
else 0 end
from @tbl
---- Without Error Query...
Select case when
id = (Case when IsNumeric(idv)=1 then idv else -1 end)
then 1
else 0 end
from @tbl
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply