help with a User-Defined function to return a string from multiple records

I need some help with writing a User-Defined function in SQL Server 2000.
I would like to return a space-delimited string, which contains the column
data of several records from a table.
Here's an example:
table_fruit
id textid
-- --
1 APPLE
2 BANANA
4 ORANGE
8 PEAR
16 PLUM
My SQL query string uses a bitwise-AND (&) to determine which records to
return.
SELECT textid FROM table_fruit WHERE ([id] & @.param_fruits) > 0
So, for example, if I pass in the parameter @.param_fruits = 13, then I get
the following records back:
APPLE
ORANGE
PEAR
What I'd like to have is a User-Defined function that returns the data in a
concatenated space-delimited string like this:
APPLE ORANGE PEAR
I need help with writing this function. Thanks very much.To get a space delimited string, you should modify the statement as follows:
=====
-- Your function declarations etc
DECLARE @.returnString VARCHAR(8000)
SET @.returnString = ''
SELECT @.returnString = @.returnString + ' ' + textid
FROM table_fruit WHERE ([id] & @.param_fruits) > 0
RETURN (@.returnString)
=====
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Scott A. Keen" <noreply@.scottkeen.com> wrote in message
news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
>I need some help with writing a User-Defined function in SQL Server 2000.
> I would like to return a space-delimited string, which contains the column
> data of several records from a table.
> Here's an example:
> table_fruit
> id textid
> -- --
> 1 APPLE
> 2 BANANA
> 4 ORANGE
> 8 PEAR
> 16 PLUM
>
> My SQL query string uses a bitwise-AND (&) to determine which records to
> return.
> SELECT textid FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> So, for example, if I pass in the parameter @.param_fruits = 13, then I get
> the following records back:
> APPLE
> ORANGE
> PEAR
> What I'd like to have is a User-Defined function that returns the data in
> a
> concatenated space-delimited string like this:
> APPLE ORANGE PEAR
> I need help with writing this function. Thanks very much.
>|||In addition, this method is unreliable and should be done on the client
side
As an alternative take a look at Erland's (if I remember well) example
CREATE PROCEDURE get_company_names_inline @.customers nvarchar(2000) AS
SELECT C.CustomerID, C.CompanyName
FROM Northwind..Customers C
JOIN inline_split_me(@.customers) s ON C.CustomerID = s.Value
go
EXEC get_company_names_inline 'ALFKI,BONAP,CACTU,FRANK'
CREATE FUNCTION inline_split_me (@.param varchar(7998)) RETURNS TABLE AS
RETURN(SELECT substring(',' + @.param + ',', Number + 1,
charindex(',', ',' + @.param + ',', Number + 1) -
Number - 1)
AS Value
FROM Numbers
WHERE Number <= len(',' + @.param + ',') - 1
AND substring(',' + @.param + ',', Number, 1) = ',')
SELECT TOP 8000 Number = IDENTITY(int, 1, 1)
INTO Numbers
FROM pubs..authors t1, pubs..authors t2, pubs..authors t3
drop table numbers
drop function inline_split_me
drop proc get_company_names_inline
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:%23mDFDKrNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> To get a space delimited string, you should modify the statement as
> follows:
> =====
> -- Your function declarations etc
> DECLARE @.returnString VARCHAR(8000)
> SET @.returnString = ''
> SELECT @.returnString = @.returnString + ' ' + textid
> FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> RETURN (@.returnString)
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott A. Keen" <noreply@.scottkeen.com> wrote in message
> news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
>|||Thanks very much! Worked great.
I had done the same query but had not declared the VARCHAR large enough, and
didn't use the SET statement to initialize the variable.
Thanks
Scott
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:%23mDFDKrNGHA.2336@.TK2MSFTNGP12.phx.gbl...
> To get a space delimited string, you should modify the statement as
follows:
> =====
> -- Your function declarations etc
> DECLARE @.returnString VARCHAR(8000)
> SET @.returnString = ''
> SELECT @.returnString = @.returnString + ' ' + textid
> FROM table_fruit WHERE ([id] & @.param_fruits) > 0
> RETURN (@.returnString)
> =====
> --
> HTH,
> SriSamp
> Email: srisamp@.gmail.com
> Blog: http://blogs.sqlxml.org/srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Scott A. Keen" <noreply@.scottkeen.com> wrote in message
> news:%23bivyHrNGHA.2036@.TK2MSFTNGP14.phx.gbl...
column
get
in
>sql