Stored Procedure - Error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored Procedure - Error

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-11-21 : 09:20:25

I have this store procedure and I am getting this error message. Not sure how to fix it. Can some one help me? Thanks.

Msg 536, Level 16, State 5, Line 1
Invalid length parameter passed to the SUBSTRING function.
The statement has been terminated.

UPDATE MonthEndProviders
SET [DoctorID] = LEFT(DoctorID, LEN(DoctorID) - 1)
WHERE RIGHT(DoctorID, 1) NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 09:27:50

WHERE RIGHT(DoctorID, 1) NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
AND doctorID > ''

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-21 : 09:28:23

try this out. the error is because you've some doctorid field with length as 0

UPDATE MonthEndProviders
SET [DoctorID] = LEFT(DoctorID, LEN(DoctorID) - 1)
WHERE RIGHT(DoctorID, 1) NOT IN ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9')
AND LEN(DoctorID)>0

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 09:30:06

[code]DECLARE @Sample TABLE
(
doctorID VARCHAR(2)
)

INSERT @Sample
SELECT 'p1' UNION ALL
SELECT 'z' UNION ALL
SELECT '' UNION ALL -- This line will generated an error earlier
SELECT NULL

SELECT doctorID,
LEFT(doctorID, LEN(doctorID) - 1)
FROM @Sample
WHERE doctorID LIKE '%[^0-9]'[/code]

E 12°55'05.63"
N 56°04'39.26"

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-11-21 : 09:33:14

Thank you so much! It worked :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources