Passing cyrillic character into stored procedure doesn't work Sql Server

Question

I have stored procedure

if OBJECT_ID(N'dbo.spGetProducts') is not null
drop procedure dbo.spGetProducts
go

CREATE procedure [dbo].[spGetProducts]
(
 @sort_col varchar(100), @sort_dir varchar(4),
 @start int, @num int, @filters nvarchar(2000)) as
 begin
 declare @end int
 declare    
  @res table 
  (
   row_num int,
   product_name nvarchar(max)
  )
set @end = @start+@num
insert into @res
EXEC
(
 'select * from 
  (
   select (ROW_NUMBER() OVER (ORDER BY '+@sort_col+' '+@sort_dir+')) row_num,
   * FROM (select product_name from Products where '+@filters+') as x
  ) as tmp where row_num between '+@start+' and '+@end
) select row_num, product_name from @res
 end 
go

When I execute the stored procedure with latin characters works perfectly, but when I use cyrillic characters does not return anything..

This is the execute line:

1. Latin characters - working:

   exec dbo.spGetProducts 'product_name','desc',0,50,' 
   product_name like N''%abs%'''
   

2. Cyrillic characters - not worikng:

   exec dbo.spGetProducts 'product_name','desc',0,50,' 
   product_name like N''%абв%'''
   

Also I manually set the stored procedure to use

'product_name like N''%абв%'''

into the line

select product_name from Products where '+@filters+'

i.e.

 select product_name from Products where product_name like N''%абв%'''

And this WORKS, so I don't know how to fix this..

Answer 1

As for your problem with cyrillic inside parameters you just need to put another N in front of whole filter parameter when sending it

exec dbo.spGetProducts 'product_name','desc',0,50, N'product_name like N''%абв%'''
                                                   ^
                                                   ^
                                                  here

but please also read my comment about security issues and reconsider changing your design.

SQLFiddle Demo

EDIT - a bit of explanation: If you do not specify that your input parameter is NVARCHAR it will be implicitly declared as VARCHAR, and your Cyrillic абв are lost and replaced with ??? before they even get to be passed to procedure.

If you are passing parameters from C# or VB, declaring them DBType.NVarChar should be enough to avoid the problem.

Answer 2

I am not surprised. You should be using parameters rather than concatenated SQL.

But it seems that your whole procedure needs to be re-written since you are using dynamic SQL.

Have a read here, it'll help you out.