SQL - Returning table with unknown columns
Ever wanted to return a table with unknown set of columns from your SQL stored procedure? Yes, you can. Say thanks to SQL XML.
SQL XML helps you convert the data being returned by a stored procedure or user defined function into a XML format. Using this functionality, you can work with stored procedures or functions without needing to know the table structure being returned.
Here is a sample query which will execute the function and convert the resultant table returned into a XML format,
DECLARE @Data XML
SET @Data = (SELECT * FROM dbo.yourFunction() FOR XML RAW(''Row''),ROOT(''Result''))
Assuming, yourFunction return a table with three columns, Name, Age and Gender, the resulting output will be,
<Result> <Row Namel="ABitSmart" Age="27" Gender="Male" /> </Result>
Now, dealing with a stored procedure is little bit twisted. We would not be able to do the same thing as we did with the function. The best way I figured out is, letting the stored procedure return a XML parameter as an output parameter. This XML parameter can be created in the same way as we did for the function, just replacing the function with the table being returned.
To convert a table (temporary or in memory) into XML refer to the following snippet,
-- converting a temporary table to XML format
DECLARE @Data XML
Set @Data = (Select * from #tmpTable For XML RAW('Row'),ROOT('Result'))
Or
-- converting an in-memory table to XML format
DECLARE @Data XML
Set @Data = (Select * from @tmpTable For XML RAW('Row'),ROOT('Result'))
Use the option as per your scenario.
As we see in the above approach, we did not have to bother with the column details being returned by the function or procedure. The XML being returned will have all this metadata for us. Every node of the XML returned will correspond to a table row. Each row will have the corresponding column value pair as the attribute value of the node.
I have used the above concepts in creating a generic reporting framework. The framework is built with WPF, LINQ and SQL 2008.
I have one main executing stored procedure which takes in a procedure name and returns a XML output. LINQ calls the main procedure, passing a stored procedure name. The main procedure executes (using Dynamic SQL) the passed in stored procedure name (which is the reporting procedure) and converts the returned table into a XML output. This XML output is returned to the main application i.e. LINQ.
The advantage of this approach is a loosely coupled architecture. The report procedures can be altered on the fly without having the application to be involved.
Softwares - .Net 3.5, WPF, Linq, SQL 2008
Have fun.
Tags: Converting table to XML, Returning table with unknown columns, SQL XML