CODEWITHSUNDEEP
sql-serverssms
Joe Phillips
Joe Phillips

Reputation: 51200

Nonsense Error in SQL Server Management Studio

I have this query that spawns the following error:

SELECT * FROM Quota
WHERE LEFT(QtLabel, LEN(QtLabel)-2) IN (
'1032',
'3300',
'9682'
)

Msg 536, Level 16, State 5, Line 1 Invalid length parameter passed to the SUBSTRING function.

Am I doing something wrong? It tends to show up when I use the LEN() function. Might it be a datatype issue?

Upvotes: 1

Views: 376

Answers (5)

cjk
cjk

Reputation: 46475

SELECT LEFT('MyText', -2)

will throw the same error

Upvotes: 1

AdaTheDev
AdaTheDev

Reputation: 147374

It's because some QtLabel values don't contain > 2 characters, so you end up trying to do a LEFT() with a negative value as the number to restrict to.

In your scenario, you're assuming all QtLabel values are 6 characters, so uou should do:

SELECT * FROM Quota
WHERE LEN(QtLabel) = 6
AND LEFT(QtLabel, LEN(QtLabel)-2) IN (
'1032',
'3300',
'9682'
)

Upvotes: 1

Chris Doggett
Chris Doggett

Reputation: 20777

LEFT probably uses SUBSTRING internally. What happens if the length of QtLabel is <= 2?

Upvotes: 1

Josip Medved
Josip Medved

Reputation: 3711

Are you sure that each QtLabel field is longer than 2 characters?

Upvotes: 2

Dewfy
Dewfy

Reputation: 23644

Is it possible that LEN(QtLabel) <= 2 ?

Upvotes: 7

Related Questions