0

I want to execute a stored procedure virtually and get the returned columns. I use fmtonly like below :

set fmtonly on
exec spName null
set fmtonly off

but using fmtonly caused to run all the lines of code and result of this work is ERROR. Is there any solution for doing this work?

masoud ramezani
  • 22,228
  • 29
  • 98
  • 151
  • 1
    I have no idea what "execute a stored procedure virtually" means. – Oded Nov 12 '12 at 08:42
  • 1
    I guess you mean only return the columns a stored proc will return. See http://stackoverflow.com/questions/7368864/retrieve-column-definition-for-stored-procedure-result-set for a similar question. – dash Nov 12 '12 at 08:43
  • I saw something mentioned that alluded to fmtonly will in some cases still execute the SP, but the article didn't elaborate on when/why this occurs. See this example where not only is it executing the sp, it causes strange behavior in how it is executed: http://stackoverflow.com/questions/303152/sql-server-2005-protecting-stored-procedures-from-fmtonly-mode-used-by-ms-acc – AaronLS Nov 12 '12 at 08:46
  • @Oded: I think he means that he only wants to know the fields that the SP will return, not the actual rows of data. – Treb Nov 12 '12 at 08:54

1 Answers1

3

You need to use sp_describe_first_result_set which is new to SQL Server 2012. Note that this requires you to provide the input parameters (at least the types).

In T-SQL development one is expected to know what procedures is calling and what is the expected result set. Before SQL server 2012 there was very little support for dynamic, runtime, discovery of procedure output and required parameters. This new procedure, along with others like sp_describe_undeclared_parameters can be used to create tools that need to explore the available programming API surface. The very fact that these were added to 2012 should indicate that the equivalent cannot be properly handled pre-2012. Solutions like loopback linked servers have many problems, primarily because they actually execute the code with potential disastrous effects.

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569