locked
Stored Procedure suddenly start failing RRS feed

  • Question

  • Hi All,

    We have a report running since last two years, running a stoed proc, on wednesday it suddenly start failing for one customer.

    the stored proc  is failing saying

    Msg 537, Level 16, State 2, Line 7

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    The issue is when I select the substring

    SUBSTRING(cp.ExceptionValue,PATINDEX('%Dispenser:%' , cp.ExceptionValue)+ 10, (PATINDEX('%]%' , cp.ExceptionValue)  - (PATINDEX('%Dispenser:%' , cp.ExceptionValue)+ 10)))

    it works fine but when i use the same substring to make a join it fails giving above error.

    I have tried to see if something wrong in the data set but could not find it, and I believe if anything wrong in the data set it should fail in the simple select statement as well.

    the data column exception value contains varchar , dates and numeric values. and I am filtering only varchar values.

    one more strange thing is if i get the data with the substring into a temp table and then make the join it works, currently this is how i have resolved the problem, but i want to go into the bottom of it.

    We are using SQLserver 2008R2.

    Please help

    Thanks

    Vishal.

    Sunday, October 20, 2013 10:27 PM

Answers

  • As mentioned by others, you cannot rely on the order in which the rows are processed.

    If there are any NULL values or other values that don't contain the requisite texts, you will get an error.

    If you have Outer Joins in your query, they may be a source of NULL values.

    To protect against this, you could wrap your expression in a CASE expression. For example:

    CASE WHEN cp.ExceptionValue LIKE '%Dispenser:%]%'
         THEN SUBSTRING(cp.ExceptionValue,PATINDEX('%Dispenser:%' , cp.ExceptionValue)+ 10, (PATINDEX('%]%' , cp.ExceptionValue)  - (PATINDEX('%Dispenser:%' , cp.ExceptionValue)+ 10)))
         END
    

    If you use that, and the end result has NULL values, then you have unexpected data in cp.ExceptionValue. If the end result has no NULL values, then intermediate (outer) joins have created the NULL values.


    Gert-Jan

    Monday, October 21, 2013 9:25 PM

All replies

  • ...the data column exception value contains varchar , dates and numeric values. and I am filtering only varchar values.

    one more strange thing is if i get the data with the substring into a temp table and then make the join it works...

    Well, the thing is you *think* you are filtering only varchar values first. But in fact, depending on executions plan, you might be going through all the rows of the table with the functions/expressins, and filtering happens at a later stage. That is why you experience such behavior.
    Monday, October 21, 2013 12:38 AM
  • Is that possible cp.ExceptionValue  column has NULLs?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, October 21, 2013 4:23 AM
    Answerer
  • I am also thinking in the same terms, how can the query plan change suddenly and that it works for one customer and not for other.
    Monday, October 21, 2013 11:31 AM
  • No there are not nulls in this column.
    Monday, October 21, 2013 11:33 AM
  • It seems to be space missing between two words

    Use cp.ExceptionValue + ' '

     

    Monday, October 21, 2013 12:32 PM
  • it did not work, if this would be the case then a simple select should fail as well, but it is not failing so something else is there.
    Monday, October 21, 2013 1:42 PM
  • You have a coding bug. See proof below.

    You need to use extra coding when using dynamic parameters with LEFT, RIGHT & SUBSTRING string functions. You can use NULLIF, CASE or IIF expressions to trap invalid arguments. For example -1 is invalid as second parameter of the LEFT function.

    Blog on NULLIF usage with LEFT & CHARINDEX:

    http://www.sqlusa.com/bestpractices2005/nullif/

    Use tempdb;
    SELECT ExceptionValue= Name into AlphaTest
    FROM AdventureWorks2012.Production.Product
    GO
    
    SELECT SUBSTRING(cp.ExceptionValue,PATINDEX('%Dispenser:%' , cp.ExceptionValue)+ 10, 
    (PATINDEX('%]%' , cp.ExceptionValue)  - (PATINDEX('%Dispenser:%' , cp.ExceptionValue)+ 10)))
    FROM AlphaTest cp
    GO
    /*
    Msg 537, Level 16, State 3, Line 1
    Invalid length parameter passed to the LEFT or SUBSTRING function.
    */


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Monday, October 21, 2013 3:33 PM
  • Hi

    I recreated the string with NULLIF

    SUBSTRING(cp.ExceptionValue,COALESCE(NULLIF(PATINDEX('%Dispenser:%' , cp.ExceptionValue)+ 10,-1),0), (COALESCE(NULLIF(PATINDEX('%]%' , cp.ExceptionValue),-1),0)  - (COALESCE(NULLIF(PATINDEX('%Dispenser:%' , cp.ExceptionValue)+ 10,-1),0))))--SUBSTRING(cp.ExceptionValue+'',PATINDEX('%Dispenser:%' , cp.ExceptionValue+'')+ 10, (PATINDEX('%]%' , cp.ExceptionValue+'')  - (PATINDEX('%Dispenser:%' , cp.ExceptionValue+'')+ 10)))

    but still it is same,

    On simple select with this substring I get all the rows back but when I use this substring in INNER JOIN I get the same error back.

    Thanks

    Monday, October 21, 2013 5:00 PM
  • As mentioned by others, you cannot rely on the order in which the rows are processed.

    If there are any NULL values or other values that don't contain the requisite texts, you will get an error.

    If you have Outer Joins in your query, they may be a source of NULL values.

    To protect against this, you could wrap your expression in a CASE expression. For example:

    CASE WHEN cp.ExceptionValue LIKE '%Dispenser:%]%'
         THEN SUBSTRING(cp.ExceptionValue,PATINDEX('%Dispenser:%' , cp.ExceptionValue)+ 10, (PATINDEX('%]%' , cp.ExceptionValue)  - (PATINDEX('%Dispenser:%' , cp.ExceptionValue)+ 10)))
         END
    

    If you use that, and the end result has NULL values, then you have unexpected data in cp.ExceptionValue. If the end result has no NULL values, then intermediate (outer) joins have created the NULL values.


    Gert-Jan

    Monday, October 21, 2013 9:25 PM