It all depends what you want and how your people have used the system.

I'm no IEX expert, only pull data from it, what we have works but might be different...

nice, knew there was a better way, just couldn't remember it (ORACLE has been LIFOing my SQL Server ATM)

However you did forget the ',' so

select @retstr = @retstr + case...

Try something like this, you'll obviously have to change table names and column names but it should do what you want.

It also will only return a max of 1000 characters...

Another option is to write a function (that you pass XX and YY to) that returns a comma delimited list of the distinct STR_DATA values in 'C'.

You can then call...

Easy answer, of course you can group by the STR_DATA column, this will have the effect of returning the rows aggregated by XX-YY AND the distinct values in the STR_DATA...

must be talking to myself on this thread!!

I have found a solution. Apparently their is a bug with the SET FMTONLY ON statement.

see here

This has the effect that any conditional...

OK, i have got around the performance problem by using pass through queries using OPENQUERY. What I have done is create an sp on the database that builds a dynamic...

Yeah, thanks for that, cleared a few things up for me too.....it's amazing what can be learnt by a simple little question..

Cheers

Bob

Another question that comes to mind is that all of the cursor based approaches use the following logic

open cursor

fetch next from cursor into ....

while @@fetch_status = 0 begin

...

...

Nice code but to compare the two you must use the same method.

The first part (where a cursor is slow) you insert each row into the table, one at a...

Valid points all but it doesn't get to the crux of my question in that where do people get the idea that cursors are bad.

I know I am from an...

Ah yes, because the cursor is non-static it would have to check for updates to its data before the next fetch.

If the sp is updating the data the cursor uses...

I can't understand, where has this hate of cursors come from. Is it a general DBA thing or something else.

I would have thought that using a cursor/recordset would be...

Sorry, are you saying that cursors are the best option or loading into a table variable and then iterating through it is the best option.....

Just a few code corrections (being pedantic), the insert won't work unless you provide the list of columns that you want to insert into....

...