RSS

Unicode parameters for stored procedures

11 Jun

When working on your ASP + SQL Server application you might encounter Unicode data is shown as ‘??????’. At least I did when started rewriting an old code to use Stored Procedures.

The ‘N’ prefix works great in both cases – when you execute stored procedure:

EXEC saveNames N'Даша', N'Сало'

or run a single query:

INSERT INTO Persons (firstName, surName) VALUES (N'Даша', N'Сало')

Note, we should use ‘N’ prefix on a Unicode string because otherwise SQL Server will convert it to the non-Unicode code page of the database. See the link for more details.

This common solution doesn’t work for stored procedure parameters. Look at the code bellow – if you try and add ‘N’ prefix there, you will get an error.

cmd.Parameters.Append cmd.CreateParameter ("@parsonName",adVarChar,adParamInput ,50,"Даша Сало")

What you have to do to make it work is:

  • Set the type of created parameters to adVarWChar;
  • Const adVarWChar = 202
    
    cmd.Parameters.Append cmd.CreateParameter ("@parsonName", adVarWChar,adParamInput, 50, "Даша Сало")
    
  • Declare stored procedure input parameters as nvarchar;
  • ALTER PROCEDURE SaveName (@firstName nvarchar(50), @surname nvarchar(50))
    
  • If you declare any variables inside stored procedure that works with Unicode data, these variables must have nvarchar type;
  • DECLARE @buf NVARCHAR(50)
    
  • You may also have to save ASP page in UTF-8 with Signature encoding. It depends on the way you receive Unicode data.

There is nothing new I just wanted to have this information in one place for anybody who gets the same problem.

Advertisements
 
1 Comment

Posted by on June 11, 2009 in misc

 

One response to “Unicode parameters for stored procedures

  1. Niraj Phalke

    July 31, 2009 at 4:50 pm

    I tried following code :
    Const adVarWChar = 202;
    cmd.CreateParameter (“@parsonName”, adVarWChar,adParamInput, 50, “Даша Сало”)

    But still I am not able to save data in unicode format. Any Suggestions.

    Thanks.

     

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: