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;
  • 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.

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.



Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: