CiucaS CiucaS - 1 month ago 10
SQL Question

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

Answer

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

Comments