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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.