Retrieve column definition for stored procedure result set

Question

I'm working with stored procedures in SQL Server 2008 and I've come to learn that I have to INSERT INTO a temp table that has been predefined in order to work with the data. That's fine, except how do I figure out how to define my temp table, if I'm not the one that wrote the stored procedure other than listing its definition and reading through the code?

For example, what would my temporary table look like for `EXEC sp_stored_procedure'? That is a simple stored procedure, and I could probably guess at the data types, but it seems there must be a way to just read the type and length of the columns returned from executing the procedure.

Answer 1

So let's say you have a stored procedure in tempdb:

USE tempdb; GO  CREATE PROCEDURE dbo.my_procedure AS BEGIN     SET NOCOUNT ON;      SELECT foo = 1, bar = 'tooth'; END GO 

There is a quite convoluted way you can go about determining the metadata that the stored procedure will output. There are several caveats, including the procedure can only output a single result set, and that a best guess will be made about the data type if it can't be determined precisely. It requires the use of OPENQUERY and a loopback linked server with the 'DATA ACCESS' property set to true. You can check sys.servers to see if you already have a valid server, but let's just create one manually called loopback:

EXEC master..sp_addlinkedserver      @server = 'loopback',       @srvproduct = '',     @provider = 'SQLNCLI',     @datasrc = @@SERVERNAME;  EXEC master..sp_serveroption      @server = 'loopback',      @optname = 'DATA ACCESS',     @optvalue = 'TRUE'; 

Now that you can query this as a linked server, you can use the result of any query (including a stored procedure call) as a regular SELECT. So you can do this (note that the database prefix is important, otherwise you will get error 11529 and 2812):

SELECT * FROM OPENQUERY(loopback, 'EXEC tempdb.dbo.my_procedure;'); 

If we can perform a SELECT *, we can also perform a SELECT * INTO:

SELECT * INTO #tmp FROM OPENQUERY(loopback, 'EXEC tempdb.dbo.my_procedure;'); 

And once that #tmp table exists, we can determine the metadata by saying (assuming SQL Server 2005 or greater):

SELECT c.name, [type] = t.name, c.max_length, c.[precision], c.scale   FROM sys.columns AS c   INNER JOIN sys.types AS t   ON c.system_type_id = t.system_type_id   AND c.user_type_id = t.user_type_id   WHERE c.[object_id] = OBJECT_ID('tempdb..#tmp'); 

(If you're using SQL Server 2000, you can do something similar with syscolumns, but I don't have a 2000 instance handy to validate an equivalent query.)

Results:

name      type    max_length precision scale --------- ------- ---------- --------- ----- foo       int              4        10     0 bar       varchar          5         0     0 

In Denali, this will be much, much, much easier. Again there is still a limitation of the first result set but you don't have to set up a linked server and jump through all those hoops. You can just say:

DECLARE @sql NVARCHAR(MAX) = N'EXEC tempdb.dbo.my_procedure;';  SELECT name, system_type_name     FROM sys.dm_exec_describe_first_result_set(@sql, NULL, 1); 

Results:

name      system_type_name --------- ---------------- foo       int              bar       varchar(5)       

Until Denali, I suggest it would be easier to just roll up your sleeves and figure out the data types on your own. Not just because it's tedious to go through the above steps, but also because you are far more likely to make a correct (or at least more accurate) guess than the engine will, since the data type guesses the engine makes will be based on runtime output, without any external knowledge of the domain of possible values. This factor will remain true in Denali as well, so don't get the impression that the new metadata discovery features are a be-all end-all, they just make the above a bit less tedious.

Oh and for some other potential gotchas with OPENQUERY, see Erland Sommarskog's article here:

http://www.sommarskog.se/share_data.html#OPENQUERY