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

Ranjith Jain 11

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.

Viorel 112.9K Reputation points

2022-06-15T14:30:22.61+00:00

Maybe your current VB code can be adjusted. Show some details.
Tom Phillips 17,716 Reputation points

2022-06-15T19:54:18.907+00:00

What do you mean "but once the parameter value gets into the stored procedure the unicode character gets converted and it wont go through for further querying"

Please post your stored proc code.

Also the results of SELECT @@VERSION.
Ranjith Jain 11 Reputation points

2022-06-16T06:00:52.457+00:00
Below is my VB.net code.

**Dim cmd As New SqlCommand cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "sp_IMS_RP_GetItemDetail" Dim itemid As String = "φ736" cmd.Parameters.AddWithValue("@ItemID", itemid)**

When i look up at cmd.tostring i get the command string as EXEC sp_IMS_RP_GetItemDetail @ItemID = 'φ736' which seems fine until now.

But when the value reaches my stored procedure the value get converted to f736. Below is the portion of stored procedure.

**Create PROC [dbo].[sp_IMS_RP_GetItemDetail] (@ItemID as nvarchar(1000) ) AS Begin

Print @ItemID

end**

When the above stored procedure is excecuted i see the below result.
Ranjith Jain 11 Reputation points

2022-06-16T06:01:29.897+00:00
Below is my VB.net code.

**Dim cmd As New SqlCommand cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "sp_IMS_RP_GetItemDetail" Dim itemid As String = "φ736" cmd.Parameters.AddWithValue("@ItemID", itemid)**

When i look up at cmd.tostring i get the command string as EXEC sp_IMS_RP_GetItemDetail @ItemID = 'φ736' which seems fine until now.

But when the value reaches my stored procedure the value get converted to f736. Below is the portion of stored procedure.

**Create PROC [dbo].[sp_IMS_RP_GetItemDetail] (@ItemID as nvarchar(1000) ) AS Begin

Print @ItemID

end**

When the above stored procedure is excecuted i see the below result.
Ranjith Jain 11 Reputation points

2022-06-16T06:04:01.627+00:00
Below is my VB.net code.

**Dim cmd As New SqlCommand cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "sp_IMS_RP_GetItemDetail" Dim itemid As String = "φ736" cmd.Parameters.AddWithValue("@ItemID", itemid)**

When i look up at cmd.tostring i get the command string as EXEC sp_IMS_RP_GetItemDetail @ItemID = 'φ736' which seems fine until now.

But when the value reaches my stored procedure the value get converted to f736. Below is the portion of stored procedure.

**Create PROC [dbo].[sp_IMS_RP_GetItemDetail] (@ItemID as nvarchar(1000) ) AS Begin

Print @ItemID

end**

When the above stored procedure is excecuted i see the below result.
Ranjith Jain 11 Reputation points

2022-06-16T06:04:08.783+00:00
Below is my VB.net code.

**Dim cmd As New SqlCommand cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "sp_IMS_RP_GetItemDetail" Dim itemid As String = "φ736" cmd.Parameters.AddWithValue("@ItemID", itemid)**

When i look up at cmd.tostring i get the command string as EXEC sp_IMS_RP_GetItemDetail @ItemID = 'φ736' which seems fine until now.

But when the value reaches my stored procedure the value get converted to f736. Below is the portion of stored procedure.

**Create PROC [dbo].[sp_IMS_RP_GetItemDetail] (@ItemID as nvarchar(1000) ) AS Begin

Print @ItemID

end**

When the above stored procedure is excecuted i see the below result.
Tom Phillips 17,716 Reputation points

2022-06-16T17:52:28.403+00:00

That is just the PRINT command changing it to non-unicode. That does not mean it is not working.

11 answers

Naomi 7,361 Reputation points

2022-06-17T14:27:51.24+00:00

I didn't look into your procedure code, but you can use STRING_SPLIT function to separate your list of items into a table. What you posted as the beginning of the SP is already not written correctly. Even if you would need to resort to dynamic SQL, you need to use it properly. The SQL variable needs to be declared as nvarchar(max). But most likely you don't need dynamic SQL in your procedure even if number of parameters is big and you're passing comma-delimited list of items.
Please sign in to rate this answer.
Ranjith Jain 11 Reputation points

2022-06-20T10:30:35.747+00:00

i see string_split is part sql 2016 and above. but we work on sql 2008 r2 and above.

Erland Sommarskog 102.2K Reputation points MVP

2022-06-20T21:07:28.463+00:00

In that case you use a user-defined functoin, see https://www.sommarskog.se/arrays-in-sql.html. And you do this, no matter if you are building the query dynamically, or you are using static SQL with OPTION (RECOMPILE).

it was a neat presentation.

Thank you!
Sign in to comment

Share via

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

**Create PROC [dbo].[sp_IMS_RP_GetItemDetail] (@ItemID as nvarchar(1000) ) AS Begin

Print @ItemID

**Create PROC [dbo].[sp_IMS_RP_GetItemDetail] (@ItemID as nvarchar(1000) ) AS Begin

Print @ItemID

**Create PROC [dbo].[sp_IMS_RP_GetItemDetail] (@ItemID as nvarchar(1000) ) AS Begin

Print @ItemID

**Create PROC [dbo].[sp_IMS_RP_GetItemDetail] (@ItemID as nvarchar(1000) ) AS Begin

Print @ItemID

11 answers