unicode in select statement - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

unicode in select statement

Author

Topic

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2007-11-23 : 16:10:48

m working on a database using arabic data, need to insert data in a table from old version of my database, containing a table

empID empName empRelation empFName empSEX
1 ABC Èä XYZ male
2 JKL ÈäÊ MNO female

new table is

empID empName empRelation empFName empSex
1 ABC 1 XYZ male
2 JKL 2 MNO female

EXEC('INSERT INTO DB1.SCHEMA1.EMP ([empID],[empName],[empRelation],[empSEX]) SELECT ([empID],[empName],CASE WHEN [empRelation]=''Èä'' THEN 1 WHEN [empRelation]=''ÈäÊ'' then 2 END,[empSEX] FROM DB2.SCHEMA1.EMP ')

case statement should return 1 for Èä and 2 for ÈäÊ but statement takes Èä & ÈäÊ as '???'
query returns correct result when executed with out EXEC() but i need to use it with EXEC() bcos of unexpected database name from end user

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-23 : 16:36:49

try using nvarchar
like below:

WHEN [empRelation]= N''Èä''

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2007-11-25 : 02:49:25

thanx dear
it works when i execute it separatly, without using EXEC(), but i need to use it in EXEC(), there it shows '????'

empRelation NVARCHAR()30

and i also used it N''Èä''

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-25 : 16:19:43

Why need exec()? It's not dynamic sql.

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2007-11-26 : 13:35:41

yes its some type of dynamic sql. Thanx Anyway
i got the solution.
i made a function instead of CASE, and its working....

Thanx team

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-11-26 : 19:46:37

set the variable

Declare @mynvar nvarchar(20)
Declare @Sql nvarchar(200)
set @Mynvar = N'Èä'
set @Sql = N'Select * from Mytable where mycolumn = '

Exec (@Sql + @MyNvar)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources