Cursors aren't recommended I know but...

Is there any other choice?
Here's what I'm working with. I have to concacentate together a series of
description fragments into one value. The table I'm working with has a
primary & foreign key, as well as a field containing the 'line' number, and
another containing the 48 character description. The descriotion could be
from 1 to 4 to 8 or any number of lines, where each line has a primary key
and ties into a foreign key.
Example:
FKey: 55
PKey: 20
Line: 1
Desc: Outside Delivery Services regarding Federal
FKey: 55
PKey: 21
Line: 2
Desc: Express Corporation.
Obviously you'd want to return the whole description as: Outside Delivery
Services regarding Federal Express Corporation.
Now imagine a description could be even 7 or 8 lines, or just 1. Without
using a cursor (which I did and it takes 2-3 minutes to return back), is
their a way to return back into a variable or result set the full
description?
Thanks!Why can't the client loop through the rows and do the concatenation itself?
Loop through, if it's the same FKey as the previous then append the line,
else write out the full row detail.
(Or, why not change the design so that the whole description is contained in
one column.)
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Steve Schroeder" <sschroeder@.somewhere.com> wrote in message
news:Oy%23LWgRQFHA.3140@.tk2msftngp13.phx.gbl...
> Is there any other choice?
> Here's what I'm working with. I have to concacentate together a series of
> description fragments into one value. The table I'm working with has a
> primary & foreign key, as well as a field containing the 'line' number,
> and
> another containing the 48 character description. The descriotion could be
> from 1 to 4 to 8 or any number of lines, where each line has a primary key
> and ties into a foreign key.
> Example:
> FKey: 55
> PKey: 20
> Line: 1
> Desc: Outside Delivery Services regarding Federal
> FKey: 55
> PKey: 21
> Line: 2
> Desc: Express Corporation.
> Obviously you'd want to return the whole description as: Outside Delivery
> Services regarding Federal Express Corporation.
> Now imagine a description could be even 7 or 8 lines, or just 1. Without
> using a cursor (which I did and it takes 2-3 minutes to return back), is
> their a way to return back into a variable or result set the full
> description?
>
> Thanks!
>|||Fair questions certainly.
I can't change the database structure as it is a vendor's database we use
for our accounting system, and changing the structure wold violate our
support agreement I'm sure.
The client program in this case is going to be an ASP.Net web application.
I've already got 99% of the sql stored procedure written and returning what
I want, just need to add the description. It was my plan to have the ASP.Net
Datagrid display the results of my stored proc (which I've done before and
have no problems handling).
So does SQL Server and T-SQL have another option for me to accomplish this.
All the logic doesn't *have* to stay in a procedure, but I would like it to
obviously.
Thanks
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:etsjpjRQFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Why can't the client loop through the rows and do the concatenation
itself?
> Loop through, if it's the same FKey as the previous then append the line,
> else write out the full row detail.
> (Or, why not change the design so that the whole description is contained
in
> one column.)
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Steve Schroeder" <sschroeder@.somewhere.com> wrote in message
> news:Oy%23LWgRQFHA.3140@.tk2msftngp13.phx.gbl...
of
be
key
Delivery
>|||Better if you do that in the client app. Here you will find some solutions.
http://groups-beta.google.com/group...
5bf366dd9e73e
AMB
"Steve Schroeder" wrote:

> Is there any other choice?
> Here's what I'm working with. I have to concacentate together a series of
> description fragments into one value. The table I'm working with has a
> primary & foreign key, as well as a field containing the 'line' number, an
d
> another containing the 48 character description. The descriotion could be
> from 1 to 4 to 8 or any number of lines, where each line has a primary key
> and ties into a foreign key.
> Example:
> FKey: 55
> PKey: 20
> Line: 1
> Desc: Outside Delivery Services regarding Federal
> FKey: 55
> PKey: 21
> Line: 2
> Desc: Express Corporation.
> Obviously you'd want to return the whole description as: Outside Delivery
> Services regarding Federal Express Corporation.
> Now imagine a description could be even 7 or 8 lines, or just 1. Without
> using a cursor (which I did and it takes 2-3 minutes to return back), is
> their a way to return back into a variable or result set the full
> description?
>
> Thanks!
>
>|||You can try a kludge like this:
http://www.aspfaq.com/2529
However, you are likely going to notice some significant performance deltas
compared to doing this where it belongs...
In the meantime, you should talk to the vendor about having them hire some
database people.
This is my signature. It is a general reminder.
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Steve Schroeder" <sschroeder@.somewhere.com> wrote in message
news:O36igoRQFHA.3144@.tk2msftngp13.phx.gbl...
> Fair questions certainly.
> I can't change the database structure as it is a vendor's database we use
> for our accounting system, and changing the structure wold violate our
> support agreement I'm sure.
> The client program in this case is going to be an ASP.Net web application.
> I've already got 99% of the sql stored procedure written and returning
> what
> I want, just need to add the description. It was my plan to have the
> ASP.Net
> Datagrid display the results of my stored proc (which I've done before and
> have no problems handling).
> So does SQL Server and T-SQL have another option for me to accomplish
> this.
> All the logic doesn't *have* to stay in a procedure, but I would like it
> to
> obviously.
> Thanks
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
> news:etsjpjRQFHA.2604@.TK2MSFTNGP10.phx.gbl...
> itself?
> in
> of
> be
> key
> Delivery
>|||Heh...yeah, but they brag about how they have world-wide fortune 500
companies using their database...chumps.
Anyhow...looks like the best thing to do would be to return two datasets,
then populate the datagrid in ASP.Net with the descriptions. Anything else
to too slow or too kludgey. Thanks Billy <roll eyes>
But thank you Aaron & Alejandro
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ulOphtRQFHA.2136@.TK2MSFTNGP14.phx.gbl...
> You can try a kludge like this:
> http://www.aspfaq.com/2529
> However, you are likely going to notice some significant performance
deltas
> compared to doing this where it belongs...
> In the meantime, you should talk to the vendor about having them hire some
> database people.
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
> "Steve Schroeder" <sschroeder@.somewhere.com> wrote in message
> news:O36igoRQFHA.3144@.tk2msftngp13.phx.gbl...
use
application.
and
line,
contained
series
a
number,
could
primary
>|||Actually...I could update the ado dataset using a loop before I bind the
result set to the datagrid couldn't I? Via VB.Net & ADO code. That would
probably be quickest, as you suggested.
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:etsjpjRQFHA.2604@.TK2MSFTNGP10.phx.gbl...
> Why can't the client loop through the rows and do the concatenation
itself?
> Loop through, if it's the same FKey as the previous then append the line,
> else write out the full row detail.
> (Or, why not change the design so that the whole description is contained
in
> one column.)
> --
> This is my signature. It is a general reminder.
> Please post DDL, sample data and desired results.
> See http://www.aspfaq.com/5006 for info.
>
>
> "Steve Schroeder" <sschroeder@.somewhere.com> wrote in message
> news:Oy%23LWgRQFHA.3140@.tk2msftngp13.phx.gbl...
of
be
key
Delivery
>|||Steve,
Another option to do is something like
select isnull(line1.desc,'') + isnull(line2.desc,'') + ... +
isnull(line8.desc,'')
from desc line1
left join desc line2 on line1.fkey=line2.fkey and line1.line=line2.line+1
left join desc line3 on line2.fkey=line3.fkey and line2.line=line3.line+1
..
left join desc line8 on line7.fkey=line8.fkey and line7.line=line8.line+1
where line1.fkey=55
Upside is great index usage. Downside is limited number of lines.
-- Alex Papadimoulis
"Steve Schroeder" wrote:

> Is there any other choice?
> Here's what I'm working with. I have to concacentate together a series of
> description fragments into one value. The table I'm working with has a
> primary & foreign key, as well as a field containing the 'line' number, an
d
> another containing the 48 character description. The descriotion could be
> from 1 to 4 to 8 or any number of lines, where each line has a primary key
> and ties into a foreign key.
> Example:
> FKey: 55
> PKey: 20
> Line: 1
> Desc: Outside Delivery Services regarding Federal
> FKey: 55
> PKey: 21
> Line: 2
> Desc: Express Corporation.
> Obviously you'd want to return the whole description as: Outside Delivery
> Services regarding Federal Express Corporation.
> Now imagine a description could be even 7 or 8 lines, or just 1. Without
> using a cursor (which I did and it takes 2-3 minutes to return back), is
> their a way to return back into a variable or result set the full
> description?
>
> Thanks!
>
>