By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,419 Members | 1,584 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,419 IT Pros & Developers. It's quick & easy.

assigning each record to one string

P: n/a
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

Jul 23 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Dear 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.

ng*****@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


Jul 23 '05 #2

P: n/a
Why not do that client-side? SQL isn't the best place for this kind of
presentational functionality.

--
David Portas
SQL Server MVP
--

Jul 23 '05 #3

P: n/a
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

Jul 23 '05 #4

P: n/a
(ng*****@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.