Pass non english value as parameter to sql procedure from c#

Question

User-257070954 posted

HI all,

           How can we pass non English values as parameter to sql stored procedure from c#?

    My requirement is, if one customer search product his own language other than english i want to display. For this purpose i am using one parameter in procedure.

Below i am giving my procedure.Please let me know where i need to make change

ALTER PROCEDURE [dbo].[GETALLProduct]
@Product NVARCHAR(100)=NULL,
@Category VARCHAR(5)=NULL
AS
BEGIN

SELECT Id,ProductName, Image AS Image2, Price,Brand ,UOM
FROM tblProduct
WHERE (@Product IS NULL OR ProductName LIKE '%' + @Product + '%' OR Brand LIKE '%' + @Product + '%' )AND (@Category IS NULL OR CategoryId=@Category)
OR (@Product IS NULL OR ProdMal LIKE N'%' + @Product + '%' OR BrandMal LIKE N'%' + @Product + '%' ) FOR JSON PATH
END

 

Answer 1

User-939850651 posted

Hi binustrat,

First, make sure that your database can store unicode and that your database uses the correct collation settings.

Second, use Nvarchar to store the value of the column, and add the prefix "N" in front of the fuzzy query condition, like this:

ALTER PROCEDURE [dbo].[GETALLProduct]
@Product NVARCHAR(100)=NULL,
@Category VARCHAR(5)=NULL
AS
BEGIN

SELECT Id,ProductName, Image AS Image2, Price,Brand ,UOM
FROM tblProduct
WHERE (@Product IS NULL OR ProductName LIKE N'%' + @Product + '%' OR Brand LIKE N'%' + @Product + '%' )AND (@Category IS NULL OR CategoryId=@Category)
OR (@Product IS NULL OR ProdMal LIKE N'%' + @Product + '%' OR BrandMal LIKE N'%' + @Product + '%' ) FOR JSON PATH
END

And I found a case named How to pass SQL stored procedure NVARCHAR parameter with Hebrew? ,it is very similar to your request, I think you can refer to it for more details.

Best regards,

Xudong Peng

Answer 2

User-257070954 posted

Hi XuDong Peng,

              Thanks for response . As per your suggestion i tried below query that's not working

Declare @Product NVARCHAR(50)
SET @Product='അരി' -- This is my language
select * from tblProduct where  ProdMal LIKE N'%' +@Product + '%'

            But below query working as expected

Declare @Product NVARCHAR(50)
SET @Product=N'അരി'
select * from tblProduct where  ProdMal LIKE '%' +@Product + '%'

  IF  i get non english parameter,  tried a non english value prefixed with N. But not working. Below is query

Declare @Product NVARCHAR(50)
Declare @Product2 NVARCHAR(50)
SET @Product='അരി'
SET @Product2='N'+@Product
select * from tblProduct where  ProdMal LIKE N'%' +@Product2 + '%'

So here my question is how we prefix with N, if we get non english parameter value. Because my second query worked as expected

Answer 3

User-939850651 posted

Hi binustrat,

I created a simple example. In fact, there is no need to manually add the prefix 'N' to use stored procedures.

You need to make sure that the type of parameters and fields is Nvarchar.

Please refer to code below:

using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["constr"].ToString()))
            {
                using (SqlCommand cmd = new SqlCommand("mulLanguageProc",conn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddWithValue("@Product", "അരി");
                    conn.Open();
                    DataTable dt = new DataTable();
                    SqlDataAdapter sda = new SqlDataAdapter(cmd);
                    sda.Fill(dt);
                }
            }

create table tblProduct(
[Id] int identity(1,1) primary key,
[ProductName] Nvarchar(50) not null,
[Image] Nvarchar(255) not null,
[Price] float not null,
[Brand] Nvarchar(50) not null
)

create proc mulLanguageProc
@Product NVARCHAR(100)=NULL
AS
BEGIN
SELECT Id,ProductName, [Image], Price,Brand
FROM tblProduct
WHERE (@Product IS NULL OR ProductName LIKE '%' + @Product + '%' OR Brand LIKE '%' + @Product + '%' )
END

Result:

Best regards,

Xudong Peng