using nvarchar & ntext datatypes
-
Dear All,
I am working on a website which is likely to go multi-lingual in a month or so. I have some queries on the multi-lingual stuff. I gave couple of examples below to indicate what I am trying to achieve :
Example1:
CREATE Procedure up_add_employee
@emp_id int,
@emp_name nvarchar(100),
@emp_cv_data ntext,
@result smallint
AS
Insert Into tblEmployee
(emp_id, emp_name, emp_cv_data, emp_date_created)
Values
(@emp_id, @emp_name, @emp_cv_data, getdate())
If @@ERROR <> 0
Set @result = -1
Else
Set @result = 0
GO
my question is, while inserting the data, do I need to prefix the variables with letter "N" ? Please note that I have already declared them as nvarchar/ntext.
---------------------------------------------------------------------------
Example2:
CREATE Procedure up_get_employee_data
@emp_id int = Null,
@emp_reg_date datetime = Null
AS
Declare @sql nvarchar(2000)
SET @sql = 'Select emp_name, emp_cv_data
From tblEmployee'
If @emp_id Is Not Null
Set @sql = @sql + ' And emp_id = ' + cast(@emp_id as varchar)
If @emp_reg_date Is Not Null
Set @sql = @sql + ' And emp_date_created > ' + cast(@emp_reg_date as varchar)
Exec sp_executesql @sql
GO
my question is, while executing the SQL, do I need to prefix the dynamic sql string with letter "N" ? Also, what will happen to the variables which I am casting to varchar? Please note that I have already declared the variable @sql as nvarchar.
---------------------------------------------------------------------------
Any help in this direction is greatly appreciated.
PK
-
This was removed by the editor as SPAM
-
Anytime you expect to have unicode characters you need to use the N.
IE:
DECLARE @v-2 nvarchar(255)
DECLARE @t TABLE
(
strWord nvarchar(255)
)
SET @v-2 = N'?'
INSERT INTO @t
VALUES(@v)
SELECT * FROM @t
I hope the Japanese character shows up OK
Also, I would instead cast the dates to nvarchar. Since your whole string needs to be nvarchar in the end it will make it much less prone to error out later. Also, I find that by making all my variables nvarchar I never worry about errors in my sp's due to data conversion problems. I have to deal with Japanese, French, German, Spanish, Chinese and English on a daily basis in my databases. So long as I remember to use the N I never have a problem with the data.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply