Hello.
So, once a day my work has a load of scheduled tasks run, basically performing a SELECT on the database, converting that data into XML and sending to the relevant external organisations.
The problem is that sometimes this takes ages, and has even crashed the server in the past. So I'm trying to optimise it. There's a lot of very straightfoward stuff I can do (the existing stored procedure is an utter nightmare of temporary tables and all sorts) but one easy win (or I hope) will be to shrink the number of columns returned.
Now, different external organisations want different columns. At the moment, it returns all of them into the C# program then selectively picks them out. But can I write a stored procedure that returns custom columns? i.e.:
SQL code:
SELECT @FIELDS FROM TABLE WHERE etc = etc
I'm going to assume not, because it seems too easy (I'm going to give it a try once I bloody get SQL2008 installed on Vista)... so, any suggestions?