Tuesday, March 11, 2008

User Define Function For Converting Column Value in Row Format

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER FUNCTION [dbo].[udf_GetAddressBookTitle](@AddressId int)
RETURNS @TitleTbl TABLE (Title varchar(1000))
AS

BEGIN

Declare @TitleTblVal varchar(50)
Declare @TitleCombinedVal varchar(1000)
Declare getAllTitleOfAddressBook cursor for
Select Title From AddressBookTitle Where AddressId = @AddressId
Open getAllTitleOfAddressBook
Fetch Next From getAllTitleOfAddressBook Into @TitletblVal
Set @TitleCombinedVal = ''
While (@@FETCH_STATUS=0)
BEGIN
Set @TitleCombinedVal = @TitleCombinedVal + @TitleTblVal +'
'
Fetch Next From getAllTitleOfAddressBook Into @TitletblVal
END
Insert Into @TitleTbl values(@TitleCombinedVal)

RETURN

END

No comments: