Function to replace a sub query?

[FinalPrime]

need some direction,
For reasons beyond my control, on some key reports, management requires my removing sub queries from my SQL's.
(In short, I'm up the creek & Dilbertized). I'm in search of all possible alternatives, including replacing each sub query with a user-defined function. Input would be the ConnectSring and the SQL. Output would the required information. Although a tremendous resource hog,
is this technically possible?
SQL SERVER 2000

any input would be appreciated
final prime

 

[Golom]

It is sometimes possible but you need to do it on a case-by-case basis. I know of no completely general prescription for replacing sub-queries with functions. Note however that, in most cases, the function is going to need to contain its own query (i.e. the sub query that you have just eliminated.)

Just out of curiosity, what is the rationalle behind this?

If you were a mechanic, would they tell you that you couldn't use pliers?

 

[FinalPrime]

I can explain this only with Dilbert sequential logic:

1) FinalPrime(me) writes Reports for myself and others using Access, Crystal , etc.

2) Management buys software from a vendor. The vendor tells management that "any Crystal Report can be made part of THE system by placing the report in their 'ReportsModule'"

3) Vendor finally admits to me (finalprime) that if I use a SubQuery in a Crystal report, it CAN NOT run (that is successfully) from their Reports Module.

4) Management now understands the Vendor's Reports Module is not as swell as alleged.

5) Management still want's some of the key reports to be made part of Vendor's Reports Module.

The Vendor's ReportsModule is limited and some of these Crystals can be very complex due to the Vendor's poor
database design. However, sub queries help me easily overcome poor design; but I'm yet able to overcome
the vendor's limited Reports Module because the sub's
must be dropped.

That makes me a mechanic trying to fix an Edsle, parked inside a garage with no lights, owning tools to fix a Porsche.

Example of what the 'work-around' might look like:

SELECT account.Table, ( MyFunction(account.Table, coonectString) as NeededData FROM Table

MyFunction would have the SQL hardcoded lacking only the
variable 'account' . For that matter, the connectstring
might also be hard coded.

Any brain-storming is appreaciated
finalprime