CODEWITHSUNDEEP
asp.netsql-server-2008stored-proceduresunicode
Learning
Learning

Reputation: 20001

How to save Unicode text using stored procedure in SQL Server 2008

I am working on an multilingual web application being developed in VS 2010 - ASP.Net (C#).

I can insert it without any problem if I use a simple insert statement without stored procedure for example

Insert INTO Test (E,A) VALUES('Some English Text',N'الرَّحْمَٰنِ الرَّحِيمِاللَّهِ الرَّحْمَٰنِ الرَّحْمَٰنِ الرَّحِيمِاللَّهِ الرَّحْمَٰنِ الرَّحْمَٰنِ الرَّحِيمِاللَّهِ الرَّحْمَٰنِ الرَّحْمَٰنِ الرَّحِيمِاللَّهِ الرَّحْمَٰنِ')

It works fine when I add N for unicode data. My question is how can I add this 'N' in stored procedure so that it can save data without any issues. I tried to put N in different way but stored procedure give syntax error

Below is the example of stored procedure I am using.

ALTER PROCEDURE [dbo].[usp_AddNewPageDetails]
    -- Add the parameters for the stored procedure here
            @PageNameEnglish varchar(100),
            @PageNameArabic nvarchar(300),
            @PageTitleEnglish varchar(300),
            @PageTitleArabic nvarchar(900),
            @PageDescEnglish varchar(400),
            @PageDescArabic nvarchar(1200),
            @PageKeywordsEnglish varchar(120),
            @PageKeywordsArabic nvarchar(400),
            @PageBodyEnglish varchar(MAX),
            @PageBodyArabic nvarchar(MAX),
            @PageLinkPosition int,    
AS
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
    SET NOCOUNT ON;

BEGIN
    INSERT INTO pg_Pages(
    PageNameEnglish,PageNameArabic,PageTitleEnglish,PageTitleArabic,PageDescEnglish,
    PageDescArabic,PageKeywordsEnglish,PageKeywordsArabic,PageBodyEnglish,
    PageLinkPosition,PageLayoutPosition,PageBannerImageEnglish,)
    VALUES
    (@PageNameEnglish,@PageNameArabic,@PageTitleEnglish,@PageTitleArabic,@PageDescEnglish,
    @PageDescArabic,@PageKeywordsEnglish,@PageKeywordsArabic,@PageBodyEnglish,)
END

Reply is appreciated.

Upvotes: 1

Views: 7360

Answers (2)

Steve D
Steve D

Reputation: 578

Can you confirm the data types of the columns in table pg_Pages are NVARCHAR where appropriate? If the columns are VARCHAR then unicode text will be converted to junk without warning.

Upvotes: 0

devio
devio

Reputation: 37215

The N is part of a Unicode string literal, just as the quotes. Probably you tried to add an N with the @parameter name, but this is not necessary (even wrong).

So calling your SP in T-SQL using syntax like 

EXEC usp_AddNewPageDetails 'english text', N'arab text', etc.

is sufficient.

If you call the SP from .Net via SqlCommand etc., no additional code is required, as .Net always uses Unicode strings.

Upvotes: 4

Related Questions