sql-server - 如何将“N”添加到存储过程

Question

这个问题背后的背景是我正在尝试在我的 WinForm 项目中使用 dapper 调用存储过程。我想让存储过程处理 if else 语句。我在使用 NVARCHAR 字段的参数之一时遇到了一些麻烦。

详细说明如下：

CREATE PROCEDURE Account_GetAccount @Code VARCHAR(4), @Name NVARCHAR(20), @AccountType_Code CHAR(1)
AS
BEGIN
IF(@AccountType_Code = '')
    BEGIN
        SELECT * FROM [dbo].[Account]
        WHERE [Code] LIKE '%' + @Code + '%'
        AND [Name] LIKE '%' + @Name + '%'
    END
ELSE
    BEGIN
        SELECT * FROM [dbo].[Account]
        WHERE [Code] LIKE '%' + @Code + '%'
        AND [Name] LIKE '%' + @Name + '%'
        AND [AccountType_Code] = @AccountType_Code
    END
END

EXECUTE Account_GetAccount @Code = '', @Name = N'現', @AccountType_Code = '';

如果我在“现”前面加上 N，那么我可以正确得到结果。但是，如果我省略了 N 则不会返回任何结果...

我想将 N 移动到存储过程中。有没有办法做到这一点？

我努力了

ALTER PROCEDURE Account_GetAccount @Code VARCHAR(4), @Name NVARCHAR(20), @AccountType_Code CHAR(1)
AS
BEGIN
IF(@AccountType_Code = '')
    BEGIN
        SELECT * FROM [dbo].[Account]
        WHERE [Code] LIKE '%' + @Code + '%'
        AND [Name] LIKE N'%' + @Name + '%'
    END
ELSE
    BEGIN
        SELECT * FROM [dbo].[Account]
        WHERE [Code] LIKE '%' + @Code + '%'
        AND [Name] LIKE N'%' + @Name + '%'
        AND [AccountType_Code] = @AccountType_Code
    END
END

EXECUTE Account_GetAccount @Code = '', @Name = '現', @AccountType_Code = '';

和

ALTER PROCEDURE Account_GetAccount @Code VARCHAR(4), @Name NVARCHAR(20), @AccountType_Code CHAR(1)
AS
BEGIN
IF(@AccountType_Code = '')
    BEGIN
        SELECT * FROM [dbo].[Account]
        WHERE [Code] LIKE '%' + @Code + '%'
        AND [Name] LIKE '%' + 'N' + @Name + '%'
    END
ELSE
    BEGIN
        SELECT * FROM [dbo].[Account]
        WHERE [Code] LIKE '%' + @Code + '%'
        AND [Name] LIKE '%' + 'N' + @Name + '%'
        AND [AccountType_Code] = @AccountType_Code
    END
END

EXECUTE Account_GetAccount @Code = '', @Name = '現', @AccountType_Code = '';

但都失败了

标签： sql-serverstored-procedures