Optionally use for XML in SQL Server

Question

I've got a question that has been bugging me for a while now, but I've not been able to find any suitable answers to my specific situation.

Currently I'm working with a set of stored procedures that have a parameter called AsXML that tells the proc to return the results as an XML document. Now this is fine and works well, but the way we are achieving this isn't terribly nice. The whole query is built up in a string and executed with the EXEC command. Personally I think it's horrible as it makes the more complicated procedures difficult to debug and really is an eyesore. It's also very difficult to explain to new employees who have little to no SQL experience. There must be a nicer way to do it.

I've already thought of using two separate stored procedures or having the calling code turn the table result into an XML document, but neither of these fly with the higher-ups who have the last say. I was hoping someone would be able to come up with another solution that would tick all the right boxes.

The solution needs to:

• Work in a single stored procedure
• Produce XML or a standard table result set based on a parameter
• Not use dynamic SQL
• Not have us writing the query out twice (one with the FOR XML and one without)

Any thoughts, suggestions or solutions?

Answer 1

One idea would be to build the result and stuff it into a #temp table.

CREATE PROCEDURE dbo.foo
    @AsXML BIT = 0
AS
BEGIN
    SET NOCOUNT ON;

    SELECT * INTO #blat FROM <complicated query>;

    IF @AsXML = 1
    BEGIN
        SELECT * FROM #blat FOR XML PATH ...;
    END
    ELSE
    BEGIN
        SELECT * FROM #blat;
    END 
END
GO