Result parameters and types of a stored procedure?

Question

Post reply

Result parameters and types of a stored procedure?

ben.brugman

SSChampion

Points: 13369
More actions
October 13, 2011 at 7:41 am

#258775

How can you get the names and the types of a result set from a stored procedure.
(For example if the result set of a stored procedure is a table).
For example for external software how does that determine the names and types.
Thanks for your time and attention,
Ben Brugman

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

External applications generally invoke the procedure and determine the result sets shape from metadata.

You can use a "dirty trick" to achieve the same thing:

DECLARE @srv nvarchar(4000)

SET @srv = @@SERVERNAME -- gather this server name

-- Create the linked server

EXEC master.dbo.sp_addlinkedserver

@server = N'LOOPBACK',

@srvproduct = N'SQLServ',

@provider = N'SQLNCLI',

@datasrc = @srv

-- Set the authentication to "current security context"

EXEC master.dbo.sp_addlinkedsrvlogin

@rmtsrvname = N'LOOPBACK',

@useself = N'True',

@locallogin = NULL,

@rmtuser = NULL,

@rmtpassword = NULL

With this linked server in place, you can query the output of a stored procedure as if it was a table, given that you prefix the stored procedure call with “SET FMTONLY OFF”, as I did in the job step script.

This technique is extremely useful when you have to capture the output of a command without knowing exactly how it will be formed:

USE tempdb

GO

CREATE PROCEDURE unknownResults

AS

BEGIN

SET NOCOUNT ON

SELECT name FROM sys.objects

END

GO

SELECT *

INTO tempdb.dbo.unknownResults_output

FROM OPENQUERY(LOOPBACK, 'SET FMTONLY OFF; EXEC tempdb.dbo.unknownResults')

Running this script will create a table named “unknownResults_output” in the tempdb database: you can find it in your object explorer and script it out to a new query editor window.

Hope this helps

Gianluca

-- Gianluca Sartori

ben.brugman SSChampion Points: 13369 More actions · Answer 2

Gianluca Sartori (10/13/2011)
External applications generally invoke the procedure and determine the result sets shape from metadata.
You can use a "dirty trick" to achieve the same thing:
With this linked server in place, you can query the output of a stored procedure as if it was a table, given that you prefix the stored procedure call with “SET FMTONLY OFF”, as I did in the job step script.
This technique is extremely useful when you have to capture the output of a command without knowing exactly how it will be formed:
Running this script will create a table named “unknownResults_output” in the tempdb database: you can find it in your object explorer and script it out to a new query editor window.
Hope this helps
Gianluca

Thanks for the quick response.

Should the stored procedure call be done with "SET FMTONLY OFF", or should it be ON ?

Problem with OFF is that the resultset can be GIGAHUGE.

Problem with ON is that the stored procedure might result in an Error.

(For us another problem is the parameters which should have a value).

Thanks again for your solution. Your solution does provide a RUN TIME solution, where my own solution did not work at runtime. (My solution: Change the stored procedure so that it produces the temptable, change the where clause into 1 = 2).

With the number of stored procedures to tackle your solution will be a great help.

Thanks,

Ben Brugman

spaghettidba SSC Guru Points: 105732 More actions · Answer 3

You're welcome, glad I could help.

Honestly, I have never tried to use FMTONLY ON to test the output, but I guess it could work.

Yes, changing the procedure to return no values at all could be a good idea too.

Good luck with your project.

-- Gianluca Sartori