Getting Return Metadata from Stored Procedures
November 9th 2008 11:11 am
As many of you (should) know, you can select result sets from stored procedures to return them to your application, but there is no built-in metadata that tells you the specifications of the columns in each result set. However, I was always intrigued by the way the LINQ to SQL designer managed to determine, without flaw, the names, data types, etc. of the columns returned by stored procedures.
So to satisfy my curiosity I set up a trace using the SQL Server profiler against the database on my box and played with the LINQ to SQL designer to get it to find that metadata. Much to my surprise, it calls the stored procedure directly, passing NULL for each of its parameters.
Wait a minute; my stored procedure does validation, so it will raise an error when xyz parameter is NULL. I then noticed earlier in the trace a strange statement that involved the SET FMTONLY statement. Searching for that statement revealed this gem.
"Returns only metadata to the client. Can be used to test the format of the response without actually running the query."
Running something like this would then yield the metadata of the result set(s), regardless of whether errors are raised.
SET FMTONLY ON;
EXEC dbo.MyTestSproc @param1 = NULL, @param2 = NULL, @param3 = NULL
SET FMTONLY OFF;
The nice thing is that you can do this for any query, not just stored procedure executions.
Syndication
Jason responded on 11 Nov 2008 at 6:55 am #
I’ve always wanted to know how that worked! But I can’t say that it works ‘without flaw’. We have a handful of crazy stored procedures at work that confuse LinqToSQL. For example, if you call another stored procedure from within your stored procedure (using exec) then it doesnt know what columns get returned anymore and doesnt return any.
Larry Cole responded on 14 Apr 2009 at 3:46 pm #
Set fmtonly on does work for most stored procedures. However another situtaion in which it does not work correctly is if your output is based on temporary tables defined in the stored procedure.