can any solve ny problem iam facing error in a funtion?

  • here is the function which i wrote

    ALTER FUNCTION [dbo].[FN_GetBizAddressDetails6](@AdspaceId int)

    RETURNS varchar(1000)

    Begin

    Declare @Building Varchar(200)

    Declare @street Varchar(200)

    Declare @landmark Varchar(200)

    Declare @City Varchar(200)

    Declare @State Varchar(200)

    Declare @CountryName Varchar(200)

    Declare @Country Varchar(200)

    Declare @PinCode Varchar(200)

    Declare @ResidenceAddress varchar(max)

    --select A.Landmark,A.City,A.CountryCode,A.Pincode,A.State from tblAdContactInfo A WHERE AdSpaceId =169

    Set @Building =( select AddressLine1 from tblAdContactInfo where AdspaceId=@AdspaceId and ContactType='p')

    Set @street=( select AddressLine2 from tblAdContactInfo where AdspaceId=@AdspaceId and ContactType='p')

    Set @landmark=( select Landmark from tblAdContactInfo where AdspaceId=@AdspaceId and ContactType='p')

    Set @City=( select City from tblAdContactInfo where AdspaceId=@AdspaceId and ContactType='p')

    Set @State=( select [State] from tblAdContactInfo where AdspaceId=@AdspaceId and ContactType='p')

    Set @CountryName=(select CountryCode from tblAdContactInfo where AdspaceId=@AdspaceId and ContactType='p')

    SET @Country=(select CountryName FROM tblCountry WHERE CountryCode =@CountryName)

    Set @PinCode=( select Pincode from tblAdContactInfo where AdspaceId=@AdspaceId and ContactType='p')

    IF(@Building ='' and @street='' and @landmark='' and @City='' and @State='' and @Country='' and @PinCode='')

    Begin

    Set @ResidenceAddress=''

    End

    ELSE

    IF(@Building<>'')

    Begin

    Set @ResidenceAddress=@Building+','

    End

    ELSE

    Begin

    Set @ResidenceAddress=''

    End

    IF(@street<>'')

    Begin

    Set @ResidenceAddress=@ResidenceAddress+@street+','

    End

    ELSE

    Begin

    Set @ResidenceAddress=@ResidenceAddress+''

    End

    IF(@landmark<>'')

    Begin

    Set @ResidenceAddress=@ResidenceAddress+@landmark+','

    End

    ELSE

    Begin

    Set @ResidenceAddress=@ResidenceAddress+''

    End

    IF(@City<>'')

    Begin

    Set @ResidenceAddress=@ResidenceAddress+@City+','

    End

    ELSE

    Begin

    Set @ResidenceAddress=@ResidenceAddress+''

    End

    IF(@State<>'')

    Begin

    Set @ResidenceAddress=@ResidenceAddress+@State+','

    End

    ELSE

    Begin

    Set @ResidenceAddress=@ResidenceAddress+''

    End

    IF(@Country<>'')

    Begin

    Set @ResidenceAddress=@ResidenceAddress+@Country+','

    End

    ELSE

    Begin

    Set @ResidenceAddress=@ResidenceAddress+''

    End

    IF(@PinCode<>'')

    Begin

    Set @ResidenceAddress=@ResidenceAddress+@PinCode+''

    End

    ELSE

    Begin

    Set @ResidenceAddress=LEFT(@ResidenceAddress, LEN(@ResidenceAddress) - 1)

    End

    Return @ResidenceAddress

    End

    i found one error when i run this function

    Msg 537, Level 16, State 5, Line 1

    Invalid length parameter passed to the LEFT or SUBSTRING function.

    cany any one solve my problem

  • Well firstly I would suggest that you discard that function entirely. If that's intended to be called as part of a larger query it's going to perform abysmally, at best.

    The error is because in the line where you use LEFT (Set @ResidenceAddress=LEFT(@ResidenceAddress, LEN(@ResidenceAddress) - 1)), the length you're passing is less than 0. Probably because @ResidenceAddress is null or empty string somewhere. You need to make sure that it's length is > 0 before applying that LEFT.

    Not even sure what that's supposed to do.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • pls try below code

    you are find the error.

    step 1:

    declare @par varchar(10)=''

    select LEFT(@par,len(@par)-1)

    step2:

    declare @par1 varchar(10)='abd'

    select LEFT(@par1,len(@par1)-1)

  • you can reduce that whole mess to a 'set based' solution, even better use it to create a view!

    set @ResidenceAddress = (select case isnull(AddressLine1) when '' else Addressline1+ ',' end

    + case isnull(addressline2) when '' else addressline2+ ',' end

    + case isnull(Landmark) when '' else Landmark+ ',' end

    + case isnull(city) when '' else city+ ',' end

    + case isnull(state) when '' else state + ',' end

    + case isnull(countryname) when '' else countryname + ',' end

    + case isnull(pincode) when '' else pincode

    from tblAdContactInfo

    left join tblCountry on tblAdContactInfo.countrycode = tblcountry.countrycode

    where AdspaceID = @Adspaceid and Contacttype='p')

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply