sql server programming: How to concat select results in SP -- sql server programming

Re: How to concat select results in SP

Roel Schreurs
12/18/2004 10:22:41 PM

Hi David,

You could use a cursor. See the following sample code.

ALTER PROCEDURE ConcatAllTheValues
AS
BEGIN
DECLARE @Name VARCHAR(50)
DECLARE @Result VARCHAR(1000)

DECLARE crs CURSOR
FAST_FORWARD
FOR
SELECT Name
FROM myTable
WHERE Age=3

OPEN crs

FETCH NEXT FROM crs INTO @Name
SET @Result = ''
WHILE @@FETCH_STATUS != -1
BEGIN
SET @Result = @Result + ';' + @Name
FETCH NEXT FROM crs INTO @Name
END

SELECT @Result

CLOSE crs
DEALLOCATE crs
END

Good luck,

Roel Schreurs
The Netherlands

David Scemama schreef:

[quoted text, click to view]

Re: How to concat select results in SP

Bob Barrows [MVP]
12/19/2004 8:21:23 AM

[quoted text, click to view]
Test both methods and see for yourself. We really cannot answer in a vacuum.
My gut says that doing a set-based operation on the server would be more
efficient than a looping operation in the client, but:

1. I'm not really sure this behavior (it's been referred to as aggregate
concatenation, but MS will not give it an official name) qualifies as a
set-based operation - the engine may resort to a cursor loop behind the
scenes to accomplish it.
2. You may be using a client that is really efficient at looping
3. Looping can be avoided through the use of the ADO recordset GetString
method, which can build a string very efficiently.
4. It really is not supported, and you would be taking a risk using it.
Having said that, I used it in a couple of early projects (SQL 6.5), before
I "knew better", and have never had a problem. The projects have been ported
first to SQL7, then to SQL 2000 without any rewrites. Just don't try to
control the order of the items that end up in the list - that is where
problems can potentially occur.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: How to concat select results in SP

David Portas
12/19/2004 11:46:51 AM

I doubt it, because none of the reliable methods in SQL is particularly
efficient.

--
David Portas
SQL Server MVP
--

Re: How to concat select results in SP

David Scemama
12/19/2004 11:59:07 AM

[quoted text, click to view]

I really understand that doing the concatenation on the client allows
more control on the operation. But in terms of performance, isn't there
a real benefit in doing it on the server side ?

Thanks

Re: How to concat select results in SP

Max
12/19/2004 4:47:58 PM

To determine which is better, you need to know:
average result set size - length and width
call frequency - average and peak
Server CPU% willing to sacrifice for this call - in $
IIS CPU% and memory willing to sacrifice for processing - in $
overall execution speed for cold and warm starts

Run the two and do he math.

I'm willing to bet a nickle the most cost efficient choice is
set-based...

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.