I am searching nvarchar text in my stored procedure. nvarchar text to be searched is passed as parameter.
dummy table
DECLARE @tb TABLE (Id INT, Name NVARCHAR(100));
insert into @tb VALUES (1,'Aman');
insert into @tb VALUES (2,N'سانديب');
insert into @tb VALUES (3,'Anuj');
insert into @tb VALUES (4,N'فيكاس');
insert into @tb VALUES (5,N'اليت');
insert into @tb VALUES (6,'Sunil');
insert into @tb VALUES (7,'Ajay');
insert into @tb VALUES (8,N'فيشال');
insert into @tb VALUES (9,'Tarun');
--select * from @tb
DECLARE @str NVARCHAR(50)=N'سانديب'
SELECT * FROM @tb WHERE Name LIKE '%'+ @str +'%'
--It is working fine
I know we need to prefix N' to search nvarchar text. But how can i add this in parameter to stored procedure
What I have tried:
DECLARE @str1 NVARCHAR(50)='سانديب'
SELECT * FROM @tb WHERE Name LIKE '%'+ @str1 +'%'
it is not working
DECLARE @str2 NVARCHAR(50)='سانديب';
SELECT * FROM @tb WHERE Name LIKE '%' + N'' + @str2 + '%'
it is still not working