Solved

Handle substring length error in SQL function

Posted on 2008-10-12
5
790 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
  • 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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

919 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now