Handle substring length error in SQL function

Hi
I have a sql function which on occasion returns an error that an invalid length parameter in the substr on occasion, how can I change the function below to return an empty string if it fails.
I'm wanting to call the procedure as update userdata set surname = dbo.udf_propvalue(username, 'Surname')

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER function [dbo].[udf_PropValue]
      (@UserName nvarchar(256), @PropName nvarchar(32))
Returns nvarchar(64)
AS
Begin
 
      Declare            @profile      nvarchar(max), -- ntext in the table ... but I won't use that
                        @PropNames      nvarchar(max),
                        @PropStart      int, -- starting index of property
                        @PropLength      int, -- length of text to retrieve
                        @iTemp            int, @iTemp2      int, @iTemp3 int, -- temp integers for some math
                        @PropValue      nvarchar(64) -- the value to return
set @PropLength = -1
set @PropStart=-1
 
      Select @profile = P.PropertyValuesString, @PropNames = P.Propertynames
            From aspnet_Profile P Inner Join aspnet_Users U on U.UserID = P.UserID
            Where U.username = @UserName
 
      if @PropNames IS NULL -- Properties not found for user
      BEGIN
            set @PropValue = NULL
      END
      Else
      Begin
            --Locate the property name
            set @iTemp = PatIndex('%' + @PropName + '%', @PropNames)
            if @iTemp = 0 --Property name not found
            Begin
                  set @PropValue = NULL
            End
            Else
            Begin
                  --Locate the property start position
                  set @iTemp2 = charindex(':',substring(@PropNames,@iTemp + len(@PropName)+3,len(@PropNames)-@iTemp))-1
                  set @PropStart = convert(int,substring(@PropNames,@iTemp + len(@PropName)+3,@iTemp2))
 
                  --Locate the property Length
                  set @iTemp3 = charindex(':',substring(@PropNames, @iTemp + len(@PropName) + @iTemp2+4 ,len(@PropNames)-@iTemp2 - @iTemp-3))-1
                  set @PropLength = convert(int,substring(@PropNames,@itemp + @iTemp2 + len(@PropName) + 4,@iTemp3 ))
 
                  set @PropValue = substring(@Profile, @PropStart+1, @PropLength)
            End
      End
 
      Return (lTrim(rTrim(@PropValue)))
End

Start Free Trial