March 26, 2012 at 2:22 pm
ALTER FUNCTION [dbo].[FN_GetBizAddressDetails](@AdInfoId int)
RETURNS varchar(1000)
Begin
Declare @Building Varchar(200)
Declare @street Varchar(200)
Declare @landmark Varchar(200)
Declare @City Varchar(200)
Set @Building =( select [Value] from tblAdContactInfo where AdInfoId=@AdInfoId and ContactTypeId=5)
Set @street=(select [Value] from tblAdContactInfo where AdInfoId=@AdInfoId and ContactTypeId=6)
Set @landmark=(select [Value] from tblAdContactInfo where AdInfoId=@AdInfoId and ContactTypeId=7)
Set @City=(select [Value] from tblAdContactInfo where AdInfoId=@AdInfoId and ContactTypeId=8)
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
Return @ResidenceAddress
End
if i execute this query means
select [dbo].[FN_GetBizAddressDetails](1)
it provide out like this
building,test ,test,Åland ,
and if i execute this query means
select [dbo].[FN_GetBizAddressDetails](2)
cnai, AC Block ,
to avoid null value i just used this comma function but in the table one customer will insert even value itself for example only bulding only
so i am trying out put like this
building,test ,test,Åland
cnai, AC Block
so plz try to help me to avoid the comma for the last value
[/code]
March 26, 2012 at 2:27 pm
Doubt it.
March 26, 2012 at 2:31 pm
try this at the end, instead of the wordier version; i'm using isnull to do the same basic logic as the if statements.
--even this test is not necessary...but left so you can nderstand
IF(@Building ='' and @street='' and @landmark='' and @City='' and @State='' and @Country='' and @PinCode='')
Set @ResidenceAddress=''
ELSE
@ResidenceAddress = 'Address:'
+ ISNULL( ',' + @Building,'')
+ ISNULL( ',' + @street,'')
+ ISNULL( ',' + @landmark,'')
+ ISNULL( ',' + @City,'')
Lowell
March 26, 2012 at 2:34 pm
Can you please look at your post? You keep placing your text within IFCode Shortcuts... Why? Put only your SQL in the [ code = "sql" ][ /code ] and leave everything else normal. It makes it very hard to read your posts.
Jared
CE - Microsoft
March 26, 2012 at 2:38 pm
Look at using the REVERSE function, something like this:
DECLARE @T VARCHAR(50)
SET @T = 'building,test ,test,Åland ,'
SELECT REVERSE(SUBSTRING(REVERSE(@T),2,LEN(@T)))
Result:
building,test ,test,Åland
March 26, 2012 at 2:52 pm
I would also avoid the four round trips to tblAdContactInfo:
;
WITH cte AS
(
SELECT [Value],ContactTypeId
FROM tblAdContactInfo
WHERE AdInfoId=@AdInfoId AND ContactTypeId IN (5,6,7,8)
)
Select
@Building= MAX(CASE WHEN ContactTypeId = 5 THEN [Value] ELSE NULL END),
@street= MAX(CASE WHEN ContactTypeId = 6 THEN [Value] ELSE NULL END),
@landmark= MAX(CASE WHEN ContactTypeId = 7 THEN [Value] ELSE NULL END),
@City= MAX(CASE WHEN ContactTypeId = 8 THEN [Value] ELSE NULL END)
FROM cte
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply