question

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

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://docs.microsoft.com/en-us/sql/t-sql/data-types/nchar-and-nvarchar-transact-sql?WT.mc_id=DP-MVP-5001259&view=sql-server-ver15

     **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)**

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

     **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)**

It would have helped if you had shown the VB code where you submit the command. But if your VB code is producing

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

you are doing it wrong.

The code should look something like this:

cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText sp_IMS_RP_GetItemDetail
cmd.Parameters.Add("@ItemID", SqlDbTypes.NVarChar, 20).Value = "φ736'"
cmd.ExecuteReader   ' Or DataAdatperFill or whatever  you like.

Here you specify the data type of the parameter, and in this case as nvarchar.

Above Artem use AddWithValue, which is OK for calls to stored procedures, but it has some very bad effects when you use CommandType.Text. So best is to stay away from it.

     **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)**

Instead of using AddWithValue change this line to

cmd.Parameters.Add("@ItemID", SqlDbType.NVachar, 1000).value = @itemId

Double check the correct syntax for VB.NET, but as Erland said, AddWithValue may be your problem, the best method is to explicitly list the type and length of the parameter using more precise Parameters.Add method.

Also, if possible, don't use sp_ for procedure names. Try using usp_ instead for the prefix where u stands for user-defined stored procedure.

As Naomi said.

See:

https://stackoverflow.com/questions/12509231/how-to-pass-unicode-value-as-parameter-for-select-command

I am not sure that you really have an issue or if you are only confusing matters. If you have a real issue - then it is not the you are posting about, and I will try to explain why.

First, we have some SQL:

CREATE TABLE Test (str  nvarchar(20) NOT NULL, int int NOT NULL)
INSERT Test (str, int)
   VALUES(N'φ736', 10), (N'f736', 656)
go
CREATE PROCEDURE TestSP @str nvarchar(20) AS
   SELECT int FROM Test WHERE str = @str
go
EXEC TestSP N'φ736'

Create this in tempdb.

Observe that the value we get back is 10. We would get 656 if the phi is mutilated.

Then we have this very simple VB .NET program:

Imports System.Data
Imports System.Data.SqlClient

Module Test

Public Sub Main()

    Dim cn      As New SqlConnection, _
        strConn As String


    '  Connection string, change server and database!
    strConn = "Integrated Security=SSPI;Data Source=(local);Initial Catalog=tempdb"

    Try
        cn.ConnectionString = strConn
        cn.Open()
    Catch ex As Exception
        Console.Writeline(ex.Message, "Connection failed!")
        cn = Nothing
        Exit Sub
    End Try

    Dim cmd As SqlCommand = cn.CreateCommand()
    
    cmd.CommandType = System.Data.CommandType.StoredProcedure
    cmd.CommandText = "TestSP"
    Dim itemid As String = "φ736"
    cmd.Parameters.AddWithValue("@str", itemid)

    Dim retval As Int32 = cmd.ExecuteScalar()

    System.Console.WriteLine("Retval: " & retval.ToString())

    
    cn.Close()
    cn.Dispose()
    
End Sub

End Module

I have also attached this as a file. Note that you need to rename to remove the .txt extension. Also note that you may need to change the server name in the connection string.
212244-slaskvb.txt

When I run the program, the output is 10. If you get a different output, this would be because you saved the code as an ANSI file. You need to store it as a Unicode file in UTF-16 or UTF-8.

You have been showing us screenshots from SSMS. Indeed, if you run the procedure as

EXEC TestSP 'φ736'

You will get back 656 (unless you have a UTF8 collation). The data type of a string literal with N in from is varchar. With N it is nvarchar.

When you use AddWithValue, it infers the data type from the second parameter. Because .NET only have Unicode strings, AddWithValue will never infer varchar.

It is also worth observing that when you call a procedure with CommandType.StoredProcedure, there is never any EXEC command. Instead the procedure is called through RPC, Remote Procedure Call. Or more precisely, the clients sends the procedure name, and the values together with the type information. But there is no SQL command per se.

If you are indeed getting incorrect results from your program, there is something more to it. For instance, if you running your program as a console-mode program, that program will not faithfully reproduce the output from that PRINT command, because the command-line window is not Unicode aware.

So if you have a real problem with your VB program, please explain what it is.

As Tom says, there is absolutely no need to use dynamic SQL in your store procedure. Just write it as:

CREATE PROCEDURE Name @ItemID nvarchar(1000) AS
SELECT ...
FROM  tbl
WHERE itemID = @itemID

If the idea is that you want to pass a comma-separated list to the procedure, I have a short article on my web site where you can learn how to do this: https://www.sommarskog.se/arrays-in-sql.html.

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.

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.