Solved

Handle substring length error in SQL function

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
View SQL 2005 Job package 16 70
SQL Server 2012 r2 - Sum totals 2 28
SubQuery link 4 36
Parse this column 6 27
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
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.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

828 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