CODEWITHSUNDEEP
sql-servert-sqlstored-proceduresunicodehebrew
IgorM
IgorM

Reputation: 1356

How to pass SQL stored procedure NVARCHAR parameter with Hebrew?

I'm trying to pass an NVARCHAR parameter to my store procedure. The stored procedure is supposed to find all suppliers that match the specified criteria. The only problem I have is that I am trying to pass criteria that contains Hebrew.

ALTER PROCEDURE [dbo].[FindSupplier] 
    -- Add the parameters for the stored procedure here
    @search_criteria nvarchar(100) = ''
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @hebrew as bit = 0

    IF @search_criteria LIKE '%[אבגדהוזחטיחכךילמנפףערקשת]%'
    BEGIN
        SET @hebrew = 1
    END

    IF @hebrew = 0 
    BEGIN

        SELECT comn020.t_suno 'Supplier Code'
        , hebcom020.t_nama 'Supplier Name1'
            , hebcom020.t_namb 'Supplier Name2'

        FROM com020 WITH (NOLOCK)

        INNER JOIN hebcom020 WITH (NOLOCK)
            ON hebcom020.t_suno = com020.t_suno

        WHERE (LTRIM(RTRIM(com020.t_suno)) LIKE N'%' + @search_criteria + '%')
           OR (SOUNDEX(LTRIM(RTRIM(com020.t_suno))) LIKE N'%' + SOUNDEX(@search_criteria) + '%')
           OR (LTRIM(RTRIM(hebcom020.t_nama)) LIKE N'%' + @search_criteria + '%')
           OR (SOUNDEX(LTRIM(RTRIM(hebcom020.t_nama))) LIKE N'%' + SOUNDEX(@search_criteria) + '%')
           OR (LTRIM(RTRIM(hebcom020.t_namb)) LIKE N'%' + @search_criteria + '%')
           OR (SOUNDEX(LTRIM(RTRIM(hebcom020.t_namb))) LIKE N'%' + SOUNDEX(@search_criteria) + '%')

    END

    ELSE  /* hebrew */
    BEGIN
        SELECT com020.t_suno 'Supplier Code'
             , hebcom020.t_nama 'Supplier Name1'
             , hebcom020.t_namb 'Supplier Name2'

        FROM com020 WITH (NOLOCK)

        INNER hebcom020 WITH (NOLOCK)
            ON hebcom020.t_suno = com020.t_suno

        WHERE hebcom020.t_nama Collate Hebrew_CI_AI LIKE N'%' + @search_criteria + '%' Collate Hebrew_CI_AI
            OR (LTRIM(RTRIM(hebcom020.t_namb)) LIKE N'%' + @search_criteria + '%')
    END

END

When I'm trying to pass something like exec FindSupplier 'ב' the SQL server recognizes char 'ב' as '?'

Your help will be highly appreciated

UPD: exec FindSupplier N'ב' worked

UPD2: In Visual Studio need to run sp with following string

="exec FindSupplier N'" & Parameters!search_criteria.Value & "'"

Upvotes: 3

Views: 9963

Answers (1)

Solomon Rutzky
Solomon Rutzky

Reputation: 48836

The problem is simply that the string literal used in the LIKE condition is not prefixed with an N to indicate that it is a Unicode string. The following example shows the difference:

DECLARE  @search_criteria NVARCHAR(100) = N'ב';

IF @search_criteria LIKE '%[אבגדהוזחטיחכךילמנפףערקשת]%'
BEGIN
    PRINT 'WithOUT "N"-prefix';
END;

IF @search_criteria LIKE N'%[אבגדהוזחטיחכךילמנפףערקשת]%'
BEGIN
    PRINT 'WITH "N"-prefix';
END;

Returns:

WITH "N"-prefix

To more easily understand why there is this difference in behavior, consider the following:

-- when the DB has a default collation of Latin1_General_100_CI_AS_SC (code page 1252)
SELECT  '%[אבגדהוזחטיחכךילמנפףערקשת]%'
-- %[????????????????????????]%

-- when the DB has a default collation of Hebrew_100_CI_AS_SC (code page 1255)
SELECT  '%[אבגדהוזחטיחכךילמנפףערקשת]%'
-- %[אבגדהוזחטיחכךילמנפףערקשת]%

The string literals are parsed in the code page used by the default collation of the current database. If the code page can support these characters, then not prefixing with the upper-case "N" will work. But, if those characters do not exist in that code page, then they are converted into "?"s.

Upvotes: 2

Related Questions