传递给 RIGHT 函数的长度参数无效 [英] Invalid length parameter passed to the RIGHT function

问题描述

大约 2 周前，一些在过去 6 个月内一直在工作的 SQL 开始抛出错误而没有任何改变.我花了几天时间试图找出发生了什么，但它没有任何意义.

About 2 weeks ago some SQL that has been working for the past 6 months started throwing an error without anything changing. I spent a couple days trying to find out what was going on and it just did not make any sense.

最终，我只尝试了对存储过程的更改"(没有效果)，然后最终删除并重新创建它(确实修复了它).

Eventually, I tried just an "alter" on the Stored Procedure (which had no effect) and then eventually dropped and recreated it (which did fix it).

然后，2 天前，错误又回来了，这一次，即使是删除/创建也没有修复它.据我的 IT 人员说，他们在 1.5 个月内没有修补 SQL Server，所以那里没有任何变化.有没有人有任何想法或遇到过同样的问题?

Then, 2 days ago, the error came back and this time even the drop/create is not fixing it. According to my IT guys, they have not patched the SQL Servers in 1.5 months so there was nothing changed there. Does anyone have any ideas or has had the same issues?

这行代码，在一个存储过程中:

This line of code, in a stored procedure:

SELECT LabelText + Data
FROM CU_FormLayout fl
    INNER JOIN CU_Data dat ON dat.LayoutId = fl.Id
    CROSS APPLY (SELECT RIGHT(fl.xLabelText,1) RightChar) ca2
    CROSS APPLY (SELECT CASE WHEN ASCII(RightChar) BETWEEN 49 AND 57 OR 
                                ASCII(RightChar) BETWEEN 65 AND 90 OR 
                                ASCII(RightChar) BETWEEN 97 AND 122
                            THEN fl.xLabelText + ': '
                            ELSE fl.xLabelText + ' ' END LabelText) ca1

导致此错误:

传递给 RIGHT 函数的长度参数无效.

Invalid length parameter passed to the RIGHT function.

如您所见，唯一的 RIGHT() 具有硬编码长度.怎么可能无效?

As you can see, the only RIGHT() has a hard-coded length. How could it be invalid?

顺便说一句，这段代码的重点是它从一列中获取一个标签"，如果它以字母或数字结尾，则添加:"，然后添加标签所对应的数据值.否则只需在它们之间留一个空格.

BTW, the point of this code is that it takes a "Label" from one column and if it ends with a letter or number, add ": " and then the data value the label is for. Other-wise just put a space between them.

推荐答案

也许如果你找不到原因(可能是生产环境有问题)，你可以使用 SUBSTRING 重写?或者 REVERSE & 的组合左.

Perhaps if you can't find the cause (it's probably bugging production environment), you could rewrite using SUBSTRING? Or a combination of REVERSE & LEFT.

这篇关于传递给 RIGHT 函数的长度参数无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！