Hi ,
I am trying to send Unicode characters to procedure
var employess = db.spEnqGetEmployeesOfUser(filter.LogUserID, filter.StartIndex, filter.EndIndex, filterText, sortText).ToList();
Below is what i am sending in filtertext :-
EmployeeName LIKE N'%लोपे%'
CREATE PROCEDURE [dbo].[spEnqGetEmployeesOfUser](
@UserID INT,
@StartNumber INT,
@PageSize INT,
@Filter NVARCHAR(MAX),
@Sort VARCHAR(MAX)
)
AS
BEGIN
However when i try to check in my sql profileer below is what i get
exec [dbo].[spEnqGetEmployeesOfUser] @UserID=4,@StartNumber=0,@PageSize=15,@Filter='EmployeeName LIKE N''%????%''',@Sort=''
what i am doing wrong , my paramter is varchar and I am also sending varchar text only why it gets converted into ???? symbols which in case not retuning me proper records
What I have tried:
CREATE PROCEDURE [dbo].[spEnqGetEmployeesOfUser](
@UserID INT,
@StartNumber INT,
@PageSize INT,
@Filter NVARCHAR(MAX),
@Sort VARCHAR(MAX)
)
AS
BEGIN
DECLARE @Employees Table
(
RowNumber INT PRIMARY KEY,
TotalCount INT,
UserID INT UNIQUE NOT NULL,
EmployeeID INT UNIQUE NOT NULL,
EmployeeCode NVARCHAR(150) NOT NULL,
EmployeeName NVARCHAR(150),
Division NVARCHAR(150)
)
IF (@Sort IS NULL OR LTRIM(RTRIM(@Sort)) = '')
BEGIN
SET @Sort = 'EmployeeName'
END
DECLARE @SQLQuery AS NVARCHAR(MAX)
SET @SQLQuery = ' SELECT * FROM (
SELECT ROW_NUMBER() OVER(ORDER BY ' + @Sort + ') AS RowNumber,COUNT(UserID) OVER() AS TotalCount, MPR.* FROM (
SELECT u.UserID,u.EmployeeID,e.Code AS EmployeeCode,u.Name AS EmployeeName, u.Division
FROM F_UserByHierarchy(' + CAST(@UserID AS VARCHAR(15)) + ') u
INNER JOIN [User] us ON u.UserID = us.UserID
INNER JOIN Employee e ON u.EmployeeID = e.EmployeeID
INNER JOIN DesignationLevel dl ON us.DesignationLevelID = dl.DesignationLevelID
WHERE dl.IsMappingRequires = 1
) MPR '
IF (@Filter IS NOT NULL AND LTRIM(RTRIM(@Filter)) <> '')
BEGIN
SET @SQLQuery = @SQLQuery + ' WHERE ' + @Filter
END
SET @SQLQuery = @SQLQuery + ' ) MP
WHERE RowNumber BETWEEN ' + CAST((@StartNumber + 1) AS VARCHAR(15)) +
' AND ' + CAST(((@StartNumber + @PageSize)) AS VARCHAR(15))
INSERT INTO @Employees
EXEC (@SQLQuery)
SELECT * FROM @Employees
END