传递给sql server中的left或substring函数的无效长度参数 [英] invalid length parameter passed to the left or substring function in sql server

问题描述

create procedure getdata
@subcomponent varchar(50)
as
begin

declare @var varchar(50) =@subcompname
;WITH CTE AS (
    SELECT Sno, Comp, SubComp,
    LEFT(FromValue, PATINDEX('%[0-9]%', FromValue)-1) As FromLetter,
    CAST(RIGHT(FromValue, LEN(FromValue) - (PATINDEX('%[0-9]%', FromValue)-1)) as int) As FromNumber,
    LEFT(ToValue, PATINDEX('%[0-9]', ToValue)-1) As ToLetter,
    CAST(RIGHT(ToValue, LEN(ToValue) - (PATINDEX('%[0-9]%', ToValue)-1)) as int) As ToNumber
    FROM
    (
    SELECT Sno, Comp, SubComp,
       LEFT(SubComp,
          CASE WHEN CHARINDEX(' to ', SubComp) >; 0 THEN
            CHARINDEX('to ', SubComp)-1
          WHEN CHARINDEX(',', SubComp) >; 0 THEN
            CHARINDEX(',', SubComp)-1
          END
       ) FromValue,
       RIGHT(SubComp,
          CASE WHEN CHARINDEX(' to ', SubComp) > 0 THEN
            LEN(SubComp) - (CHARINDEX(' to ', SubComp) + 3)
          WHEN CHARINDEX(',', SubComp) > 0 THEN
            CHARINDEX(',', SubComp)-1
          END
       ) ToValue
    FROM T
    ) InnerQuery
 )

 SELECT Sno, Comp, SubComp
 FROM CTE
 WHERE LEFT(@Var, PATINDEX('%[0-9]%', @Var)-1) BETWEEN FromLetter AND ToLetter
 AND CAST(RIGHT(@Var, LEN(@Var) - (PATINDEX('%[0-9]%', @Var)-1)) as int) BETWEEN FromNumber And ToNumber

这是我的程序... .........



我收到错误....

this is my procedure ............

I am getting error ....

引用：

无效长度参数传递给sql server中的左边或子字符串函数

"invalid length parameter passed to the left or substring function in sql server"

请帮帮我...

please help me...

推荐答案

我真的无法回答，但我可以告诉你它与函数中的-1有关，这是一篇确定可以真正帮助你的文章



http://www.sql-server-helper.com/error- messages / msg-536.aspx [ ^ ]



尝试一下，也许吧你的回答

I cant really answer but I can tell you that it has to do with the -1 in the function, here is an article that im sure could really help you

http://www.sql-server-helper.com/error-messages/msg-536.aspx[^]

try it out, maybe it holds your answer

这个错误与你的SP中使用的LEFT函数有关，问题可能是第二个参数值没有回归正整数（请注意：PATINDEX返回还有NULL值）



这些链接可能对你有所帮助：

https://msdn.microsoft.com/en-IN/library/ms177601.aspx [ ^ ]

https://msdn.microsoft.com/en-us/library/ms188395.aspx [ ^ ]

This error is related to "LEFT" function used in your SP, the issue probably may be that the second parameter value is not returing a positive integer (Please Note : PATINDEX returns NULL value as well)

These links might help you further :
https://msdn.microsoft.com/en-IN/library/ms177601.aspx[^]
https://msdn.microsoft.com/en-us/library/ms188395.aspx[^]

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