Solved

Handle substring length error in SQL function

Posted on 2008-10-12
5
795 Views
Last Modified: 2012-05-05
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

0
Comment
Question by:sparksolutions
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 8

Accepted Solution

by:
tiagosalgado earned 500 total points
ID: 22698875
You can use a try catch block.

Begin Try
set @PropValue = substring(@Profile, @PropStart+1, @PropLength)
end try
begin catch
set @PropValue = ''
end catch

Open in new window

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22699009
Replace:
Else
   Begin
   --Locate the property start position
With:
Else If CHARINDEX(':',substring(@PropNames,@iTemp + len(@PropName)+3,len(@PropNames)-@iTemp)) > 0
   Begin
   --Locate the property start position
0
 

Author Comment

by:sparksolutions
ID: 22700151
nope,
tiagosalgado, it does not allowed timed functions inside a function
acperkins: I still get Invalid length parameter passed to the SUBSTRING function.

0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 22707952
You will have to find out the exact line it is occurring and the values that causes it.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question