query error 537 'Invalid Length/...' what is this? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

query error 537 'Invalid Length/...' what is this?

Author

Topic

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2012-01-12 : 17:54:59

When I execute the sp it returns this error when the dates are from 1/8/12 to 1/12/12 but not when the from date is 1/10/12
so that in the application is shows this error also when it's from 1/10/12 that is not so much my concern as to why this happens what is causing the error

Msg 537, Level 16, State 2, Procedure DBD_GET_MEDICINE_DETAIL, Line 15
Invalid length parameter passed to the LEFT or SUBSTRING function.
here is the query params
DECLARE @return_value int

EXEC @return_value = [OGEN].[DBD_GET_MEDICINE_DETAIL]
@FACILITYKEY = N'RI',
@DATEFROM = N'01-08-2012',
@DATETHRU = N'01-12-2012',
@UNITSTR = N'01',
@INCLUDEDISCH = 0,
@PATNUMBER = -1,
@MEDNAME = N''

SELECT 'Return Value' = @return_value

GO

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-12 : 18:27:47

holy error code batman...

You have a problem in your sproc

post the sproc

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-12 : 18:50:45

Usually that's the result of calling len or substring on an empty string when the procedure didn't expect an empty string. Often bad data or insufficient checking or parameters or data.

Check line 15 of the procedure and see what the LEN or Substring is doing and to what data.

--
Gail Shaw
SQL Server MVP

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-12 : 18:54:00

http://www.youtube.com/watch?v=65FOQpQpSwc

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 03:20:31

quote:
Originally posted by AdamWest

When I execute the sp it returns this error when the dates are from 1/8/12 to 1/12/12 but not when the from date is 1/10/12
so that in the application is shows this error also when it's from 1/10/12 that is not so much my concern as to why this happens what is causing the error

Msg 537, Level 16, State 2, Procedure DBD_GET_MEDICINE_DETAIL, Line 15
Invalid length parameter passed to the LEFT or SUBSTRING function.
here is the query params
DECLARE @return_value int

EXEC @return_value = [OGEN].[DBD_GET_MEDICINE_DETAIL]
@FACILITYKEY = N'RI',
@DATEFROM = N'01-08-2012',
@DATETHRU = N'01-12-2012',
@UNITSTR = N'01',
@INCLUDEDISCH = 0,
@PATNUMBER = -1,
@MEDNAME = N''

SELECT 'Return Value' = @return_value

GO

i think you're trying to match some pattern using CHARINDEX or PATINDEX and trying to take substring/left/right based on that. If serached character or pattern is not found,it will cause negative position value to be passed for substring function which cause this error. So add a CASE check to ensure pattern/character is present in string before you apply PATINDEX/CHARINDEX to avoid this error

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

AdamWest
Constraint Violating Yak Guru

360 Posts

Posted - 2012-01-13 : 08:07:52

here is that sp. so I put the case on this line it seems:

"CONVERT(VARCHAR, DTR.DATE_ORDER, 110) + ' ' + SUBSTRING(CONVERT(VARCHAR, AM.ADMIN_TIME, 100), 13, 7) ADMIN_TIME"

CREATE PROCEDURE [OGEN].[DBD_GET_MEDICINE_DETAIL]
(
@FACILITYKEY varchar(1000),
@DATEFROM DATETIME,
@DATETHRU DATETIME,
@UNITSTR VARCHAR(100),
@INCLUDEDISCH NUMERIC(1, 0),
@PATNUMBER NUMERIC(9, 0),
@MEDNAME VARCHAR(100)
)
AS
BEGIN
SELECT AM.FACILITY_KEY FACILITY
, M.UNIT_CODE UNIT
, RTRIM(M.LAST_NAME) + CASE WHEN RTRIM(M.FIRST_NAME) <> '' THEN ', ' ELSE '' END + RTRIM(M.FIRST_NAME) PATIENT_NAME
, CASE WHEN M.NURSING_UNIT is not null THEN M.NURSING_UNIT ELSE '' END NURSING_UNIT
, CASE WHEN M.UNIT_CODE is not null THEN M.UNIT_CODE ELSE '' END UNIT_CODE
, SUBSTRING(M.ROOM_BED, 1, CHARINDEX(' ', M.ROOM_BED) - 1) ROOM
, CONVERT(VARCHAR, DTR.DATE_ORDER, 110) + ' ' + SUBSTRING(CONVERT(VARCHAR, AM.ADMIN_TIME, 100), 13, 7) ADMIN_TIME
, AM.ORDER_NAME
, AM.DOSAGE
, AM.DOSAGE_FORM
, CASE WHEN AL.ADMIN_RESULT = -9 THEN 'Y' ELSE '' END NOTE_ENTERED
, AM.ORDER_KEY
FROM (SELECT TOP(DATEDIFF(DD, @DATEFROM, @DATETHRU) + 1) OGEN.DATEONLY(@DATEFROM) + ROW_NUMBER() OVER (ORDER BY DX_KEY) - 1 DATE_ORDER, ROW_NUMBER() OVER(ORDER BY DX_KEY) DAY_ORDER FROM OGEN.GEN_C_DX ORDER BY DX_KEY) DTR
INNER JOIN OPTC.ORD_M_ADMIN AM
ON DTR.DATE_ORDER + ( AM.ADMIN_TIME - OGEN.DATEONLY(AM.ADMIN_TIME) ) BETWEEN AM.START_DATE AND AM.END_DATE
AND (AM.START_DATE BETWEEN @DATEFROM AND @DATETHRU OR AM.END_DATE BETWEEN @DATEFROM AND @DATETHRU OR (AM.START_DATE < @DATEFROM AND AM.END_DATE > @DATETHRU))
AND AM.FACILITY_KEY IN (SELECT VALUE FROM [OGEN].[COMMA_TO_TABLE](@FACILITYKEY))
AND ( @PATNUMBER = -1 OR AM.PAT_NUMBER = @PATNUMBER )
AND ( @MEDNAME IS NULL OR @MEDNAME = '' OR AM.ORDER_NAME LIKE '%' + @MEDNAME + '%' )
AND AM.PRN = 0
INNER JOIN OGEN.GEN_M_PATIENT_MAST M
ON AM.PAT_NUMBER = M.PAT_NUMBER
AND AM.FACILITY_KEY = M.FACILITY_KEY
AND M.FACILITY_KEY IN (SELECT VALUE FROM [OGEN].[COMMA_TO_TABLE](@FACILITYKEY))
AND ( @UNITSTR IS NULL OR @UNITSTR = '' OR CHARINDEX(M.UNIT_CODE, @UNITSTR) % 2 = 1 )
LEFT OUTER JOIN OPTC.ORD_D_ADMIN_LOG AL
ON AM.ORDER_KEY = AL.ORDER_KEY
AND OGEN.DATEONLY(AL.ADMIN_TIME) = DTR.DATE_ORDER
AND AL.ADMIN_TIME - OGEN.DATEONLY(AL.ADMIN_TIME) = AM.ADMIN_TIME - OGEN.DATEONLY(AM.ADMIN_TIME)
WHERE OPTC.ORD_IS_ADMIN(AM.START_DATE, DTR.DATE_ORDER, AM.DAYS_REQ) = 1
AND NOT EXISTS ( SELECT AL1.ORDER_KEY
FROM OPTC.ORD_D_ADMIN_LOG AL1
WHERE AL1.ORDER_KEY = AM.ORDER_KEY
AND OGEN.DATEONLY(AL1.ADMIN_TIME) = DTR.DATE_ORDER
AND AL1.ADMIN_TIME - OGEN.DATEONLY(AL1.ADMIN_TIME) = AM.ADMIN_TIME - OGEN.DATEONLY(AM.ADMIN_TIME)
AND CHARINDEX(CAST(ABS(AL1.ADMIN_RESULT) AS VARCHAR(2)), '01234') > 0 )
AND NOT EXISTS ( SELECT OM.ORDER_KEY
FROM OPTC.ORD_M_ORDER OM
WHERE OM.ORDER_STATUS = 0
AND OM.HOLD_ON_FROM = AM.ORDER_KEY
AND DTR.DATE_ORDER BETWEEN OM.START_DATE
AND OM.END_DATE )
AND ( @INCLUDEDISCH = 0
OR 1 = CASE WHEN @INCLUDEDISCH = 1 AND M.DISCHARGE_DATE IS NULL THEN 1
WHEN @INCLUDEDISCH = 2 AND M.DISCHARGE_DATE IS NOT NULL THEN 1
ELSE 0 END )
ORDER BY PATIENT_NAME, M.PAT_NUMBER, DATE_ORDER, AM.ADMIN_TIME - OGEN.DATEONLY(AM.ADMIN_TIME)
END

GO

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-13 : 08:16:41

SUBSTRING(CONVERT(VARCHAR, AM.ADMIN_TIME, 100), 13, 7)

One problem straightaway, varchar without a length. Do you know what the default length is?

Now, to get that error, Admin_time would need to be less than 13 characters long. You, I assume, have checked the values in the table and ensured that it does have the value you would expect for all rows.

--
Gail Shaw
SQL Server MVP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 09:37:35

see this to understand why should specify length always

http://visakhm.blogspot.com/2010/02/importance-of-specifying-length-in.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources