Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
trouble2
Constraint Violating Yak Guru
267 Posts |
 Posted - 2010-05-10 : 10:53:24
|
| I'm rebuilding a SP which now kind of looks like this:SELECT Distinct u.UserID, u.FirstName, u.LastName, u.Username, u.Email, (select CountryName from Country Where CountryCode = upd.Country) as Country, upd.UserType, upd.Year,...I used to use countrycode, which is something like US or UK.However, I now want to use the countryID, only the id is an int, and upd.Country is a nvarchar. So when I do: (select CountryName from Country Where CountryID = upd.Country) as Country,I will get an error, like:Conversion failed when converting the nvarchar value 'US' to data type int.So what would be a good strategy?I can't count on upd.Country to always be numeric... |
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-05-10 : 11:10:39
|
Convert the CountryID into a NVARCHAR:CAST(CountryID AS NVARCHAR(30)) ------------------------------------------------------------------------------------Any and all code contained within this post comes with a 100% money back guarantee. |
 |
|
|
malpashaa
Constraint Violating Yak Guru
264 Posts |
Posted - 2010-05-10 : 11:43:25
|
Use this query as a staging query until you fix the type of up.Country to be INT (after you change all values)SELECT DISTINCT u.UserID, u.FirstName, u.LastName, u.Username, u.Email, (SELECT C.CountryName FROM Country AS C WHERE C.CountryID = CASE WHEN ISNUMERIC(upd.Country) THEN CAST(upd.Country AS INT) ELSE NULL END) as Country, upd.UserType, upd.Year,... FROM ... WHERE ISNUMERIC(upd.Country) = 1 UNION ALLSELECT Distinct u.UserID, u.FirstName, u.LastName, u.Username, u.Email, (SELECT C.CountryName FROM Country AS C WHERE C.CountryCode = upd.Country) as Country, upd.UserType, upd.Year,... FROM ... WHERE ISNUMERIC(upd.Country) <> 1 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-10 : 11:51:01
|
| I would add a new column to the table for a Country_INT, get that populated by updating based on JOINS between the old Country_CHAR and the Country lookup-table. Modify all code that used the original column name ... then DROP the old column name.Having a mix of Old/New coding systems in a single column is likely to give you some grief along the way ... |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-05-10 : 13:14:16
|
quote:
Originally posted by trouble2
<snip>I will get an error, like:Conversion failed when converting the nvarchar value 'US' to data type int.So what would be a good strategy?I can't count on upd.Country to always be numeric...
Some interesting advice has been provided so far..However, I don't think you have provided us enough information for us to help you. Unless of couse it is as simple as converthing the INT to a VARCAHR as DBA in the making has suggested.How do you map from the CountryID to the CountryName? Does the country table have both an ID, Country (presumably a code like 'US') and a COuntryName? Will upd.Country always be the Country(code) or is it a mixture of Varchar and Numeric values?This seems, on the surface, like a simple issue. Maybe some sample data would help or a more detailed description of issue. |
|
|
|
trouble2
Constraint Violating Yak Guru
267 Posts |
Posted - 2010-05-11 : 03:13:55
|
| Hi all, thank for the advice, I think I'm going for the simple solution.So I''l just convert to nvarchar.It is not the ideal solution, since some users will still be using countrycodes like 'US' but I like to keep iot simple and fast.So thanks to all...The secret to creativity is knowing how to hide your sources. (Einstein) |
|
|
|
|
|
|
|
|
Select with int or nvarchar