How to pass a unicode character as parameter for a store procedure in vb.net

Question

0

i am trying to pass a list of ids as a parameter of a stored procedure from vb.net. And these IDs can have unicode characters as well.

EXEC sp_IMS_RP_GetItemDetail @ItemID = '''736'', ''φ736'''

i am aware that if the parameter is defined as nvarchar it will go through. but once the parameter value gets into the stored procedure the unicode character gets converted and it wont go through for further querying. i was able to find out and tested as well that if i pass the parameter value by appending a N character before the value, the unicode character doesnt get converted.

EXEC sp_IMS_RP_GetItemDetail @ItemID = N'''736'', ''φ736'''

But i am failing to find a way to append a N character programmatically from vb.net.

Answer 1

OK, please check that variables in your stored procedure do have nvarchar types and appropriate collation, seems that your procedure converts Unicode to 8 bit

https://learn.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?WT.mc_id=DP-MVP-5001259&view=sql-server-ver15

Answer 2

You do not pass 'N' prefix for variables. The "N" is for static strings to indicate they are unicode.

Answer 3

Just as you don't inline parameter values in VB code, but use the parameter object, you don't inline parameter values when you build dynamic SQL strings in stored procedures, but here as well, you should parameterise your statements.

As it happens, this is a topic that I have covered extensively, and I have a longer article on my web site that discusses how to build these dynamic searches: https://www.sommarskog.se/dyn-search.html. You will learn dynamic SQL is only one possibility, and quickly looking at your code, I think that static SQL + OPTION (RECOMPILE) can be a better option in your case.

If you prefer to watch a video, you can see my presentation here: https://www.youtube.com/watch?v=4H9eE1-28yg.

Answer 4

I think i figured out the issue. Based on the feedbacks that i obtained from most of the users above i was able to figure it out. Below are the highlights of it.

1. if a parameter of a stored procedure is declared as nvarchar, the vb.net framework will take care of unicode characters. you will not need to append a character 'N' before it.
2. In order to execute the stored procedure directly on SQL Server Management Studio, you will need to append a character 'N' before the parameter value to handle unicode characters.
3. In my stored procedure, i had declared the sql query variable as varchar rather than nvarchar. By changing the datatype and by add a character 'N' before each line when you append to the sql query variable fixed the issue.

Answer 5

Try to use something like this:

Dim cmd As New SqlCommand("sp_IMS_RP_GetItemDetail", conn)  
Dim ids() As String = { "736", "φ736"}  

Try  
    cmd.CommandType = CommandType.StoredProcedure  
    cmd.Parameters.AddWithValue("@ItemID", ids)  
    Using r = cmd.ExecuteReader()  

        if r.Read() then  
           ...  
        end if  
   End Using  
Finally  
    If cmd IsNot Nothing Then cmd.Dispose()  
End Try