;
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
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
) FromValue,
RIGHT
(SubComp,
CASE
WHEN
CHARINDEX(
'
to '
, SubComp) >
0
THEN
LEN(SubComp) - (CHARINDEX(
'
to '
, SubComp) +
3
)
WHEN
CHARINDEX(
'
,'
, SubComp) >
0
THEN
CHARINDEX(
'
,'
, SubComp)-1
) 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 ....
Quote:
"invalid length parameter passed to the left or substring function in sql server"
please help me...
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
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
[
^
]
Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad
spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or
edit the question
and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it.
Provide an answer or move on to the next question.
Let's work to help developers, not make them feel stupid.
i.e. instead of :
LEFT(FromValue, PATINDEX('%[0-9]%', FromValue)-1)
try this
LEFT(FromValue, PATINDEX('%[0-9]%', FromValue))
If this does not help
Try putting ISNULL check in "Left" function
i.e. LEFT(FromValue, ISNULL(PATINDEX('%[0-9]%', FromValue),0))
do the same for all the "Left" functions used in your SP.