RIGHT函数传入无效长度参数：SQL视图创建报错求助

报错原因 #

你的SQL语句里RIGHT([A_ACCOUNT], Charindex('\', Reverse([A_ACCOUNT])) - 1)这部分触发了Msg 536错误，问题确实出在-1上：
当A_ACCOUNT字段里没有反斜杠\时，Reverse([A_ACCOUNT])里也找不到\，此时Charindex('\', Reverse([A_ACCOUNT]))会返回0，0-1=-1。而RIGHT函数的第二个参数（截取长度）不能是负数或0，直接导致报错。

解决方案 #

你需要先判断A_ACCOUNT中是否存在反斜杠，再决定截取逻辑。以下几种方式都可以解决问题：

方式一：用CASE语句明确处理两种情况 #

SELECT 
    CASE 
        WHEN Charindex('\', Reverse([A_ACCOUNT])) > 0 THEN RIGHT([A_ACCOUNT], Charindex('\', Reverse([A_ACCOUNT])) - 1)
        ELSE [A_ACCOUNT] -- 没有反斜杠时直接返回原账号，可根据业务需求调整
    END AS Account_UserKey,
    Replace(Upper(Concat('APP_', R.A_DESCRIPTION)), ' ', '_') AS Role_BusinessKey,
    'False' AS Disabled
FROM [db_application_company].[dbo].[ROLEASSIGN_P] RA
INNER JOIN dbo.LOGIN_M AS LO ON RA.I_LOGIN_M = LO.I_LOGIN_M
INNER JOIN dbo.ROLES_M R ON R.I_ROLES_M = RA.I_ROLES_M 
WHERE LO.A_ACCOUNT != '' 
  AND R.A_DESCRIPTION NOT IN ('', 'READ ONLY')

方式二：用NULLIF+ISNULL简化处理 #

SELECT 
    RIGHT([A_ACCOUNT], ISNULL(NULLIF(Charindex('\', Reverse([A_ACCOUNT])), 0) - 1, LEN([A_ACCOUNT]))) AS Account_UserKey,
    Replace(Upper(Concat('APP_', R.A_DESCRIPTION)), ' ', '_') AS Role_BusinessKey,
    'False' AS Disabled
FROM [db_application_company].[dbo].[ROLEASSIGN_P] RA
INNER JOIN dbo.LOGIN_M AS LO ON RA.I_LOGIN_M = LO.I_LOGIN_M
INNER JOIN dbo.ROLES_M R ON R.I_ROLES_M = RA.I_ROLES_M 
WHERE LO.A_ACCOUNT != '' 
  AND R.A_DESCRIPTION NOT IN ('', 'READ ONLY')

方式三：过滤掉无反斜杠的账号（如果业务允许） #

如果你的业务逻辑只需要保留带反斜杠的账号，直接在WHERE条件里加过滤即可：

SELECT 
    RIGHT([A_ACCOUNT], Charindex('\', Reverse([A_ACCOUNT])) - 1) AS Account_UserKey,
    Replace(Upper(Concat('APP_', R.A_DESCRIPTION)), ' ', '_') AS Role_BusinessKey,
    'False' AS Disabled
FROM [db_application_company].[dbo].[ROLEASSIGN_P] RA
INNER JOIN dbo.LOGIN_M AS LO ON RA.I_LOGIN_M = LO.I_LOGIN_M
INNER JOIN dbo.ROLES_M R ON R.I_ROLES_M = RA.I_ROLES_M 
WHERE LO.A_ACCOUNT != '' 
  AND R.A_DESCRIPTION NOT IN ('', 'READ ONLY')
  AND Charindex('\', LO.A_ACCOUNT) > 0

内容的提问来源于stack exchange，提问作者Embargo96