Unicode and Sql

Question

Post reply

Unicode and Sql

PC Perera

Old Hand

Points: 315
More actions
June 25, 2007 at 3:04 am

#173117

I am new to sql, I have problem with unicode data

How to pass unicode data to stored procedure (Think below SP uni)
How to insert unicode data to table using stored procedure below Uni
I use sql server 2000 personal Edition
Create Table:
/*
create Table TestTable(Col1 nvarchar(20))
*/
Stored Prucedure:
CREATE PROCEDURE Uni
@UnitName nvarchar(20)
AS
INSERT INTO TestTable(Col1) VALUES (@UnitName)
After that I execute query analyser
1. Uni 'Test' – This text going to the table, Its in English, No problem
2. Uni '#3523;#3538;#3458;#3524;#3517;' – this string contains unicode Characters – Problem, when I open the table, this charaters shows like ?????
Above displays numbers but, it is a string, I wrote unicode string, this page displays like this (Problem may be U don't have this Unicode Font, think it as a normal sting but contain Unidode Characters)
Refer this stored procedure
CREATE PROCEDURE UniNext
AS
INSERT INTO TestTable(Col1) VALUES (N'#3514;#3540;#3505;#3538; Uni String')
GO
Above displays numbers but, it is a string, I wrote unicode string, this page displays like this
I am inserting Unicode string with N prefix, It is ok
My problem again
How to pass unicode data to stored procedure (Think above SP uni)
How to insert unicode data to table using stored procedure above Uni
You can refer:
http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 1

2. Uni '#3523;#3538;#3458;#3524;#3517;'

should be

2. Uni N'#3523;#3538;#3458;#3524;#3517;'

How are you executing the SP?

PC Perera Old Hand Points: 315 More actions · Answer 2

I need to develop below stored procedure

Stored Prucedure:

CREATE PROCEDURE Uni

@UnitName nvarchar(20)

AS

I execute it using query analyzer

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 3

Are you executing as:

EXEC Uni 'unicode here'

or as

EXEC Uni N'unicode here'?

Also check the font setting in your query analyzer.

PC Perera Old Hand Points: 315 More actions · Answer 4

Thanks Koji, You are getting me,

use Query Analyzer

and

its Windows type

Uni 'unicode here'

I need to pass unicode data as a parameter to the DECLARED variable @unitname in my stored procedure (SP), SP should write the give data to table. Then I can call this SP using my programming language, I hope to use Command object in .NET, before this I want to test, then I use Query Analyzer and pass data to SP, but don't write, English strings ok, I can Directly type unicode data to colums, but cant using SP, with N prefix, I can write unicode data, But I need to pass parameter containing different values

Koji Matsumura SSCrazy Points: 2241 More actions · Answer 5

I am using English SQL Server 2000 8.00 2039 (sp4) and following works fine.

I mean usgin [varchar] and without [N] of N''.

DECLARE @T TABLE (ID int, Test varchar(10))

INSERT INTO @T VALUES (1, '???')

SELECT * FROM @T

/*

returns

1???

*/

Try the above code and let me know what happens.

Server Collation is Japanese_CI_AS

??? part displays Unicode on my PC.

vadba SSChampion Points: 11133 More actions · Answer 6

I'm not sure I understand the exact problem you are having.

Are you trying to pass unicode strings that contain characters with Unicode value such as 3523, 3538, 3458, 3524 and 3517? I'm not familiar with the syntax you are using, if that is what you are trying to do, and cannot find any reference to it.

Try this:

DECLARE @parm nvarchar(20)

SET @parm = NChar(3523) + NChar(3538) + NChar(3458) + NChar(3524) + NChar(3517)

EXEC Uni @parm