If I do understand you correctly, you want to get the data types of the result set of a stored procedure (instead of the result set itself). You can use the sa_describe_query() system procedure when using v10 or newer, e.g.
select * from sa_describe_query('select * from sa_conn_info()')
Or you can query the system catalog directly, such as
select parm_name, * from sysprocparm spp key join sysprocedure sp where parm_type = 1 and proc_name = 'sa_conn_info' order by 1
(For older versions, the query might have to be adapted.)
The above answers work nicely for procedures that have a single result set where the server is able to determine the schema for the result set when the procedure is created. This could be because the procedure describes the columns using a RESULT clause or because the server is able to determine the result set by inspecting the procedure. In the case of multiple result sets or procedures where the schema changes from call to call, you could use instead the sa_describe_cursor system procedure. This system procedure describes the current result set of an open cursor, which could be a cursor over a CALL statement. You can even use this if the procedure returns multiple result sets; just use RESUME until the cursor is positioned on the proper result set. However, unlike the other answers, this approach requires actually executing the procedure.
The sa_copy_to_temp_table system procedure can also be used if you want to copy the contents of the cursor to a temporary table (although the column names are generated as col1..colN instead of coming from the statement).
create temporary procedure P_DynamicResult( @type int ) begin if @type = 1 then select * from sys.systab; else select * from dbo.rowgenerator; end if; end; begin declare crsr cursor for call P_DynamicResult(0); open crsr; select * from sa_describe_cursor('crsr'); close crsr; end;
answered 04 Jul '11, 07:55
Ivan T. Bowman
select * into dba.MyNewTable from dba.MyCoolProcedure() where 0 = 1
answered 01 Jul '11, 04:09