July 18, 2012 at 3:13 pm
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
July 18, 2012 at 3:23 pm
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
July 18, 2012 at 9:13 pm
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)
July 20, 2012 at 8:04 am
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