SQL Server Nvarchar参数

Question

我创建了一个存储过程，根据ado发送的字符串在数据库中搜索多个表

Vb代码

Shared ReadOnly Property Connection() As String
    Get
        Return ConfigurationManager.ConnectionStrings("ConnString").ConnectionString
    End Get
End Property

Function GetData(ByVal SearchKey As String) As DataTable
    Dim sqlConn As New SqlConnection(Connection)
    Dim ds As New DataSet
    Dim sqlCmd As New SqlCommand("Search_List")
    Dim sqlAdapter As New SqlDataAdapter
    sqlCmd.CommandType = CommandType.StoredProcedure
    sqlCmd.Connection = sqlConn
    sqlAdapter.SelectCommand = sqlCmd
    sqlCmd.Parameters.Add(New SqlParameter("@SearchKey", SqlDbType.NVarChar, 255, ParameterDirection.Input, True, CByte(0), CByte(0), "SearchKey", DataRowVersion.Default, SearchKey))
    sqlCmd.Parameters.Add("RerurnValue", SqlDbType.Int)
    sqlCmd.Parameters("RerurnValue").Direction = ParameterDirection.ReturnValue
    Try
        sqlConn.Open()
        sqlAdapter.Fill(ds, "Result")
        sqlConn.Close()
    Catch ex As Exception
        Exit Function
    End Try
    Return ds.Tables("Result")
End Function

而且SQL SP是：

ALTER PROCEDURE [dbo].[Search_List](
    @SearchKey  NVARCHAR(200)   
)
AS
BEGIN
           SET NOCOUNT ON;
        DECLARE @Rc bigint
        DECLARE @Err bigint
        set @SearchKey = '%'+@SearchKey+'%'

    CREATE 
    TABLE 
        #Temp
            (
                ID              BIGINT,
                elementType     NVARCHAR(10),       
                NameAr          NVARCHAR(255),
                NAmeEn          NVARCHAR(255),
                DescAr          NVARCHAR(MAX),
                DescEn          NVARCHAR(MAX),
                URL             NVARCHAR(MAX)       
            )
    INSERT INTO #Temp 
    SELECT 
             Id
            ,'C'
            ,NameAr 
            ,NameEn 
            ,DescAr 
            ,DescEn 
            ,'Counsel.aspx'
    FROM 
            CMS_Councils  
    Where
            (NameAr like  @SearchKey    
                OR
            NameEn Like @SearchKey  
                OR
            DescAr Like @SearchKey  
                OR
            DescEn Like @SearchKey)
                AND
            isnull(Status,0) = 1    

    select * from #Temp 
    end

正如您所看到的，我在VB中声明了参数为Nvarchr，并将SQL参数@SearchKey也声明为Nvarchar。如果我在@SearchKey中发送英文数据，则搜索会返回正确的数据，但如果我尝试在@SearchKey中发送阿拉伯字符串，则没有结果出现，尽管表格中存在阿拉伯数据。

我错过了什么吗？

我该怎么做才能允许阿拉伯语搜索？

- wala rawashdeh

Answer 1

您可以尝试使用 COLLATE 关键字指定排序规则：

例如，您需要在后面指定阿拉伯语： COLLATE arabic_ci_as。

SELECT 
         Id
        ,'C'
        ,NameAr 
        ,NameEn 
        ,DescAr 
        ,DescEn 
        ,'Counsel.aspx'
FROM 
        CMS_Councils  
Where
        (NameAr like  @SearchKey    
            OR
        NameEn Like @SearchKey  
            OR
        DescAr Like @SearchKey  
            OR
        DescEn Like @SearchKey)
            AND
        isnull(Status,0) = 1    
COLLATE arabic_ci_as

- Darren