Error : Invalid length parameter passed to the LEFT or SUBSTRING function

21,702

Problem Fixed of my aboves question Its due to i forgot to mention varchar size while declaring function

Create Function dbo.fnFcid(@url varchar(max))
Share:
21,702
Ankit
Author by

Ankit

Updated on May 15, 2020

Comments

  • Ankit
    Ankit almost 4 years

    My Function is as Following ::


    Create Function dbo.fnFcid(@url varchar)
     Returns int
    
     As
    
     BEGIN
     Return ( select ID from  TTI_fat.dbo.FormContext
              where caseId = (SELECT SUBSTRING(@url, PATINDEX('%&c=%', @url)+ 3, (PATINDEX('%&y=%', @url) - (PATINDEX('%&c=%', @url)+3)))) 
              and   YearID = (SELECT SUBSTRING(@url, PATINDEX('%&y=%', @url)+ 3, (PATINDEX('%&e=%', @url) - (PATINDEX('%&y=%', @url)+3)))) 
              and   EntityID = (SELECT SUBSTRING(@url, PATINDEX('%&e=%', @url)+ 3, (PATINDEX('%&g=%', @url) - (PATINDEX('%&e=%', @url)+3))))
              and   GroupID = (SELECT SUBSTRING(@url, PATINDEX('%&g=%', @url)+ 3, (PATINDEX('%&v=%', @url) - (PATINDEX('%&g=%', @url)+3)))) 
             )  
       END;
     Go
    

    when I am passing a following value to function


    select dbo.fnFcid('http://localh0st/web/forms/getForm.asp?page=FKYASDENPLFPg1V2014&c=9&y=283&e=1022&g=2&v=1&x=296807&j=161037&z=1')
    

    its shows me following error


    Msg 537, Level 16, State 2, Line 1
    Invalid length parameter passed to the LEFT or SUBSTRING function.