Solved

Handle substring length error in SQL function

Posted on 2008-10-12
5
789 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
You will have to find out the exact line it is occurring and the values that causes it.
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

771 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

10 Experts available now in Live!

Get 1:1 Help Now