sql - 错误 :: “在动态 SQL 中将 nvarchar 值“谷物”转换为数据类型 int 时转换失败”

Question

我正在尝试在动态 SQL 中按 Id 对我的表“排序”，尽管它可以使用Name和Description（两种类型 Nvarchar），如图所示但不能使用Id（类型 INT），这是我的动态 SQL 查询

';With data AS (Select P.Id,
    P.Name,
    P.Description,
    P.Price,
    12 AS ActiveUsers,
    B.Name as BillingPeriod,
        CASE '+@SortField+'
                WHEN P.Name THEN ROW_NUMBER() OVER (ORDER BY P.Name)
                WHEN Description THEN ROW_NUMBER() OVER (ORDER BY P.Description)
                WHEN P.Id THEN ROW_NUMBER() OVER (ORDER BY P.Id)   *****This Line Gives Error*****
            END rn '
     --- REST of The SP ---

该行给出跟随错误

将 nvarchar 值 'cereals' 转换为数据类型 int 时转换失败。

我打印 SQL 它显示...

CASE P.Id
                WHEN P.Name THEN ROW_NUMBER() OVER (ORDER BY P.Name)
                WHEN Description THEN ROW_NUMBER() OVER (ORDER BY P.Description)
                WHEN P.Id THEN ROW_NUMBER() OVER (ORDER BY P.Id)
                END rn From Products P  

请帮帮我，我是 sql 新手。

标签： sqlsql-serverdynamic-sql

Answer 1

您可以将整个 CASE 表达式替换为 ...

';With data AS (Select P.Id,
    P.Name,
    P.Description,
    P.Price,
    12 AS ActiveUsers,
    B.Name as BillingPeriod,
    ROW_NUMBER() OVER (ORDER BY '+@SortField+')'
     --- REST of The SP ---