assigning each record to one string

hello,

i would like to loop through a record set and assign each value to the
same string, (example i would like to return all of the first name in
the authors table = Authors_total.)

should i use a cursor or just a loop to do this? I have had some
trouble with the syntax in a cursor.

nicholas.gadaczDear Nicholas,

I hope following will be help full for you.
---------------
Declare @.varstr as varchar(4000) -- declreation of
set @.varstr = ''; --initializing you know the fact Null + somhting =
Null
select @.varstr = @.varstr+','+isnull(ProductName,',') from Product;
Select @.varstr;
---------------

Best of Luck :) :) :)

Saghir Taj
MCDBA
www.dbnest.com: Home of DB Professionals.

ngadacz@.ftresearch.com wrote:
> hello,
> i would like to loop through a record set and assign each value to
the
> same string, (example i would like to return all of the first name in
> the authors table = Authors_total.)
> should i use a cursor or just a loop to do this? I have had some
> trouble with the syntax in a cursor.
> nicholas.gadacz|||Why not do that client-side? SQL isn't the best place for this kind of
presentational functionality.

--
David Portas
SQL Server MVP
--|||I am still not sure how i would loop through all of the records. if a
use a cursor i get an error variable assignment is not allowed in a
cursor declaration.

The reason why I don't put this functionality is the client side is
that I have multiple client sides: asp php and soon .aspx (.net) with
changes I want to have the code centralized.

nicholas.gadacz|||(ngadacz@.ftresearch.com) writes:
> I am still not sure how i would loop through all of the records. if a
> use a cursor i get an error variable assignment is not allowed in a
> cursor declaration.

DELARE @.str varchar(8000), @.col varchar(30)

DECLARE cur INSENSTIVE CURSOR FOR
SELECT col FROM tbl ORDER BY col
OPEN cur
WHILE 1 = 1
BEGIN
FETCH cur INTO @.col
IF @.@.fetch_status <> 0
BREAK

SELECT @.str = CASE WHEN @.str IS NULL
THEN @.col
ELSE @.str + ',' + @.col
EMD
END
DEALLOCATE cur

This is one of the few things where you must use a cursor. Another poster
showed an example with a SELECT statement. However, that is not guaranteed
to work.

> The reason why I don't put this functionality is the client side is
> that I have multiple client sides: asp php and soon .aspx (.net) with
> changes I want to have the code centralized.

Beware that the above solution has a hard limit of the output string of
8000 characters.

In SQL2005 there will actually be a way to do this in a single statement,
by some fairly funny usage of the new XML stuff.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp