SEO

#1 (**permalink**) 02-29-2008, 05:53 AM

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

#2 (**permalink**) 02-29-2008, 05:53 AM

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.

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

#3 (**permalink**) 02-29-2008, 05:53 AM

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

--
David Portas
SQL Server MVP
--

#4 (**permalink**) 02-29-2008, 05:54 AM

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

#5 (**permalink**) 02-29-2008, 05:54 AM

(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/techinf...2000/books.asp

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

assigning each record to one string