如何将unicode charcters传递给程序参数？ [英] How to pass unicode charcters to procedures parameters ?

问题描述

我正在尝试将Unicode字符发送给程序

Hi ,

I am trying to send Unicode characters to procedure

var employess = db.spEnqGetEmployeesOfUser(filter.LogUserID, filter.StartIndex, filter.EndIndex, filterText, sortText).ToList();

以下是我在filtertext发送的内容： -

员工姓名喜欢N'％लोपे％'

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

但是当我尝试办理登机手续时下面的sql profileer是我得到的



exec [dbo]。[spEnqGetEmployeesOfUser] @ UserID = 4，@ StartNumber = 0，@ PageSize = 15，@ Filter =' EmployeeName LIKE N''％????％'''，@ Sort =''



我做错了什么，我的参数是varchar而且我也是发送varchar文本只是为什么它被转换成????符号，以防万一不能重新录制正确的记录



我尝试过：

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)) 

--SELECT @SQLQuery
INSERT INTO @Employees 
EXEC (@SQLQuery)

SELECT * FROM @Employees
END

推荐答案

可能是SQL事件探查器无法读取该字符串，而SQL本身就可以使用它。



通过创建确定它实际上不起作用一个空表并插入unicode。检查数据库是否有效。



您可以在数组中发送unicode字符。你需要知道每个char的unicode。您可以通过以下查询获得：

It could be that SQL Profiler is unable to read that char while SQL itself is ok with it.

Check that it actually doesn't work by creating an empty table and inserting unicode into it. Check on the DB if it works.

You can send unicode chars in an array instead. You'll need to know the unicode of each char. You can get that with this query:

DECLARE @nstring nchar(12);
SET @nstring = N'लो';
SELECT UNICODE(@nstring)

在你的SQL中你必须从int解码它：

In you sql you'll have to decode it from an int:

DECLARE @uni int;
SET @uni= N'लो';
SELECT NCHAR(@uni)

这是一个非常大的黑客攻击。



查看SP参数设置为NVarChar。您可能需要查看context.designer.cs才能找到它



如果这些想法不起作用，请告诉我

That is all a massive hack tho.

Check that the SP parameters are set as NVarChar. You may have to look at the context.designer.cs to find it

Let me know if those ideas don't work

这篇关于如何将unicode charcters传递给程序参数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！