Determining data types of stored procedure result sets

I had another great question by my buddy and super-stealth, ninja, beast-mode, middle-tier, middle-layer, business objects, BLL, DAL, patterns dude, badass programmer friend Craig Boland the other day, and this one I’m afraid I can’t answer. Craig is a super-smart consultant with Sogeti – a c# guy – who’s been around awhile, and seems to always give me the most interesting and toughest questions to answer concerning SQL Server. So Craig emails and asks “How can I tell the data types of a SQL Server stored procedure result set?”. Good one! It didn’t take me long to offer-up a suggestion, but it wasn’t the one that I wanted to give, and probably not the one that he wanted to hear.

Remember that SQL Server procs can ‘return’ information in three different ways: 1) the RETURN keyword, 2) OUTPUT parameters, and 3) one or more result sets via the SELECT statement located somewhere in the body of the procedure. His question came from the fact that he was seeing an unexpected data type from <option 3> and wanted to verify this. Apparently he was using a built-in function with the return type specification of integer, but it seemed to appear to him as a decimal/numeric in the fat-client application that he is working on. (Craig is upstairs from me here at Alcon Laboratories, Inc. in Fort Worth, TX, so I get to bug the $hit out of him often).

The way I solved it…run DTSWiz and/or DTSWizard from the command line, plop the sp in the query window, and look to see what types it recognizes when you map a destination table.  Great! Um, well not so great. I really wanted to do it in TSQL and right now I don’t have a way. Can you answer this question? Send me your response. I want to see the answer derived from TSQL code, please.

Doing it this way, I just so happened to have SQL Profiler open and observed the following, and what I expected – a lot of API calls to procedures that we can’t fire from SSMS, sqlcmd, or anything else.  You can read up on these calls by checking them out on MSDN. Note the following caveat therein:

They are intended only for the internal use of the SQL Server Provider for OLE DB, the SQL Server ODBC driver, and the DB-Library DLL. The full functionality of these procedures is available to the applications through the use of the cursor functionality of the database APIs. Specifying the procedures directly in an application is not supported.

Let’s create a minimal stored procedure real quick-like and go though the exercise, and then review the profiler output.

Click ‘Edit Mappings’ and you get the following ‘Column Mappings’ dialog; below is the trace output as well for this particular click.

Investigations from some blog posts just don’t always reveal an answer to a given question, and this post is one of those posts. I’ll keep digging (I think I looked around for 30 minutes or so for an answer) until I find one. I Googled! But did not see anyone who answered this question, so if you have it an idea how to do this, give me a shout.

Thank you,

Lee Everest

-----------------------

“Rule #2: The Double Tap”