How can I insert a unicode character through stored procedue variable?

Posted on 2007-10-17
Last Modified: 2010-07-27
I have a table with an nvarchar(4000) column and i need to store speech phonemes which are unicode characters in the table through a stored procedure. I know I need to place and N' before the string being passed in. My stored procedure parameter is also set to be a varchar(8000) data type and this cannot be changed.

I have tried: set @Value ='N' + cast(@value as nvarchar(4000))  and set @Value ='N'''+ cast(@value as nvarchar(4000))  + ''''
where @value is '/Q/, /ð/'

The problem is the table shows me  N'/?/, /ð/' or N/?/, /ð/ which is not the correct characters and i do not want to store 'N' in my text column.

What am I doing wrong?

Question by:Sue_W
    LVL 1

    Accepted Solution


    set @Value =N'' + cast(@value as nvarchar(4000)) +''
    LVL 15

    Assisted Solution

    I may be mistaken, but if the input parameter is VARCHAR, I do not think there is any way you can pass in unicode data. SQL Server will automatically convert it to VARCHAR as it comes into the procedure, and it cannot be cast back to unicode. Using the N'' notation is a way to, within T-SQL, to tell SQL server that the string is unicode, but by just 'computing'  N'' around a VARCHAR string does not convert it to unicode. The results you are getting would be expected, because you are saying: concatenate the literal N (or N'), my string, converted to unicode, and ' together. Once a unicode character has been converted to ASCII, you have lost the information necessary to convert it back to unicode.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    This article describes some very basic things about SQL Server filegroups.
    Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
    Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now