*Help* Function to convert a column to a scalar value - how?

Hi,
I'm trying to consolidate a column from the 'many' side of a relationship
into a single NVARCHAR scalar value so that it can be displayed in a client
application (no editing is required).
I figured that I would be best using a user function called from within the
originating stored procedure, passing the record ID value to the function
which is then used as the foreign key within a SELECT statement on the
'many' table.
My confusion is around the looping structure within the function and
persisting a current value to append to the NVARCHAR value, which can then
be passed out to the calling stored proc.
The function currently looks like this (but doesn't work):
ALTER FUNCTION dbo.fnCofAsStringForContactID
(
@.ContactID AS INTEGER
)
RETURNS NVARCHAR(500)
AS
BEGIN
DECLARE @.TempString AS NVARCHAR(500)
DECLARE @.FullLocation AS NVARCHAR(100)
SELECT @.FullLocation = CompanyLocations.LocationName
FROM ContactCofAJoin
INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
CompanyLocations.LocationID
WHERE (ContactCofAJoin.fContactID = @.ContactID)
WHILE @.FullLocation IS NOT NULL
BEGIN
SET @.TempString = @.TempString + @.FullLocation + ', '
END
RETURN @.TempString
END
Am I correct in thinking that the WHILE loop will iterate through the SELECT
records and extract the data that I need?
Cheers,
Andyhttp://www.aspfaq.com/show.asp?id=2529
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Andrew Kidd" <nospam@.nospam.com> wrote in message
news:u$4UDNckFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm trying to consolidate a column from the 'many' side of a relationship
> into a single NVARCHAR scalar value so that it can be displayed in a
> client application (no editing is required).
> I figured that I would be best using a user function called from within
> the originating stored procedure, passing the record ID value to the
> function which is then used as the foreign key within a SELECT statement
> on the 'many' table.
> My confusion is around the looping structure within the function and
> persisting a current value to append to the NVARCHAR value, which can then
> be passed out to the calling stored proc.
> The function currently looks like this (but doesn't work):
> ALTER FUNCTION dbo.fnCofAsStringForContactID
> (
> @.ContactID AS INTEGER
> )
> RETURNS NVARCHAR(500)
> AS
> BEGIN
> DECLARE @.TempString AS NVARCHAR(500)
> DECLARE @.FullLocation AS NVARCHAR(100)
>
> SELECT @.FullLocation = CompanyLocations.LocationName
> FROM ContactCofAJoin
> INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
> CompanyLocations.LocationID
> WHERE (ContactCofAJoin.fContactID = @.ContactID)
> WHILE @.FullLocation IS NOT NULL
> BEGIN
> SET @.TempString = @.TempString + @.FullLocation + ', '
> END
>
> RETURN @.TempString
>
> END
> Am I correct in thinking that the WHILE loop will iterate through the
> SELECT records and extract the data that I need?
> Cheers,
> Andy
>|||You don't actually need a loop. Try this:
http://milambda.blogspot.com/2005/0...s-as-array.html
Adjust to your specific needs.
ML|||Many thanks for the tips guys!
"Andrew Kidd" <nospam@.nospam.com> wrote in message
news:u$4UDNckFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm trying to consolidate a column from the 'many' side of a relationship
> into a single NVARCHAR scalar value so that it can be displayed in a
> client application (no editing is required).
> I figured that I would be best using a user function called from within
> the originating stored procedure, passing the record ID value to the
> function which is then used as the foreign key within a SELECT statement
> on the 'many' table.
> My confusion is around the looping structure within the function and
> persisting a current value to append to the NVARCHAR value, which can then
> be passed out to the calling stored proc.
> The function currently looks like this (but doesn't work):
> ALTER FUNCTION dbo.fnCofAsStringForContactID
> (
> @.ContactID AS INTEGER
> )
> RETURNS NVARCHAR(500)
> AS
> BEGIN
> DECLARE @.TempString AS NVARCHAR(500)
> DECLARE @.FullLocation AS NVARCHAR(100)
>
> SELECT @.FullLocation = CompanyLocations.LocationName
> FROM ContactCofAJoin
> INNER JOIN CompanyLocations ON ContactCofAJoin.fLocationID =
> CompanyLocations.LocationID
> WHERE (ContactCofAJoin.fContactID = @.ContactID)
> WHILE @.FullLocation IS NOT NULL
> BEGIN
> SET @.TempString = @.TempString + @.FullLocation + ', '
> END
>
> RETURN @.TempString
>
> END
> Am I correct in thinking that the WHILE loop will iterate through the
> SELECT records and extract the data that I need?
> Cheers,
> Andy
>