1

I have two records in my person table in my database, their last names are "تحصیلداری" and "موقر".
when I use 

select * from Person where Lastname like N'%ی%'

the record containing "ی" character in last name returns, which is exactly what I need, but I need a little change. I need to use the word between % and % as a parameter.
Something like : 

create procedure usp_GetPersonsWhoseNameContains(@LN nvarchar(50))
as
begin
    select * from Person where Lastname like N'%'+@LN+'%'
end

declare @LastName nvarchar(50) = 'ی'
exec usp_GetPersonsWhoseNameContains(@LastName)

but it does not work this way, and in my searches I couldn't find the appropriate solution. Does anyone know how to

Mahdi Tahsildari
  • 13,065
  • 14
  • 55
  • 94

1 Answers1

2

Try 

create procedure usp_GetPersonsWhoseNameContains(@LN nvarchar(50))
as
begin
    select * from Person where Lastname like N'%'+@LN+'%'
end

declare @LastName nvarchar(50) = N'ی'
exec usp_GetPersonsWhoseNameContains(@LastName)

Maybe you noticed that SQL Server uses N to any quoted string input when generating DDL scripts for you.

CodeGuru
  • 2,722
  • 6
  • 36
  • 52
  • my problem is that I want to use this stored procedure in a C# application and fill the parameter from there, and AFAIK I cannot put an N behind it from C# App, so how should I do it? – Mahdi Tahsildari Aug 14 '13 at 12:23
  • how you passing it currently , can you give code for that? Try to put N in code side also before parameter as i put here in sql. – CodeGuru Aug 14 '13 at 12:28
  • static public List GetPersonContaining(string word) { PersonTableAdapter.GetPersonsWhoseNameContains(word); } – Mahdi Tahsildari Aug 14 '13 at 12:34
  • I have a DataSet, containing a TableAdapter for Person table, and have added a Query (using an existing stored procedure) – Mahdi Tahsildari Aug 14 '13 at 12:37
  • i am not getting you exactly but some where you using something like SqlCommand cmd = new SqlCommand("CustOrderHist", conn); // 2. set the command object so it knows to execute a stored procedure cmd.CommandType = CommandType.StoredProcedure; // 3. add parameter to command, which will be passed to the stored procedure cmd.Parameters.Add(new SqlParameter("@CustomerID", custId)); Here instead of @CustomerID try to give N@CustomerID. – CodeGuru Aug 14 '13 at 12:40