CODEWITHSUNDEEP
sqlsql-serverdelphistored-procedures
CiucaS
CiucaS

Reputation: 2128

get returned fields datatypes from a stored procedure

I've got a small problem I ran into. I want o create a form that ask for a procedure name and returns the params of the stored procedure and the columns that the procedure returns.

For example 

 CREATE PROCEDURE abc 
    @Param1 int,
    @Param2 varchar(1)
 as
    SELECT * 
    FROM TableA 
    WHERE Param1 = @Param1 and Param2 = @Param2
 GO

I know I can get the params of a procedure from SQL Server using this query 

select  
   'Parameter_name' = name,  
   'Type'   = type_name(user_type_id),  
   'Length'   = max_length,  
   'Prec'   = case when type_name(system_type_id) = 'uniqueidentifier' 
              then precision  
              else OdbcPrec(system_type_id, max_length, precision) end,  
   'Scale'   = OdbcScale(system_type_id, scale),  
   'Param_order'  = parameter_id,  
   'Collation'   = convert(sysname, 
                   case when system_type_id in (35, 99, 167, 175, 231, 239)  
                   then ServerProperty('collation') end)  

  from sys.parameters where object_id = object_id('[dbo].[abc]')

and from what I've read on the web, there is no easy way to get the returned datatypes and their names from a stored procedure as the result may be different based on the input params.

I would love to know if there is a way to get the returned datatypes and their names from SQL, but I wouldn't mind if anyone can tell me, or guide me to a place where I can find a solution to get the returned datatypes and their names from Delphi. Something like when you click on a TADOStoredProc and click on AddAllFields option.

Hope I made myself understood.

Thank you

Upvotes: 0

Views: 2742

Answers (3)

darlove
darlove

Reputation: 317

It is best not to return many different sets from one stored procedure. If one has to return many sets, it's better to create different procedures and have a uniform data set returned from each of them. Multiple result sets from one procedure is not something that should be returned. Just because something is possible it does not mean it should be made use of.

Upvotes: 0

Caffé
Caffé

Reputation: 1171

You can use sys.dm_exec_describe_first_result_set.

See the example B "Returning information about a procedure":

USE AdventureWorks2012;
GO

CREATE PROC Production.TestProc
AS
SELECT Name, ProductID, Color FROM Production.Product ;
SELECT Name, SafetyStockLevel, SellStartDate FROM Production.Product ;
GO

SELECT * FROM sys.dm_exec_describe_first_result_set
('Production.TestProc', NULL, 0) ;

http://msdn.microsoft.com/en-us/library/ff878258.aspx#code-snippet-2

It uses the same algorithm as sp_describe_first_result_set, so you can find some remarks about this at:

http://msdn.microsoft.com/en-us/library/ff878602.aspx

Upvotes: 3

Dave Mason
Dave Mason

Reputation: 4936

This is by no means a complete answer, but I wanted to show how SET FMTONLY is used. It may solve part of the OP's request.

CREATE PROCEDURE abc 
    @Param1 INT
AS
    SELECT TOP(@Param1) *
    FROM master.sys.objects
GO

--When set OFF, 3 rows are returned.
SET FMTONLY OFF
EXEC abc @Param1 = 3

--When set ON, no rows are returned, but the columns and column names of the result set are returned.
SET FMTONLY ON
EXEC abc @Param1 = 3

NOTE: if the stored proc has branching and may return different results sets, this option is not going to work. Again, this is not a complete answer, just a demo for the OP.

Upvotes: 0

Related Questions