Best Solution to get Stored Procedure returned header Names in T-SQL

Question

I have a stored procedure in Sql Server 2008 with below body, that inserts car then gets it:

ALTER PROCEDURE [dbo].[sp_Car_Insert]
    @RunDate VARCHAR(25),
    @CarNo NVARCHAR(50),
    @Volume INT,
    @Weight SMALLINT,
    @CarName NVARCHAR(50),
    @InUse BIT,
    @CarID TINYINT OUTPUT
AS
BEGIN
    SELECT @CarID = ISNULL(MAX(c.CarID), 0) + 1
    FROM   Car c

    INSERT INTO Car
      (
        CarID,
        RunDate,
        CarNo,
        Volume,
        [Weight],
        CarName,
        InUse,
        IsDeleted
      )
    VALUES
      (
        @CarID,
        @RunDate,
        @CarNo,
        @Volume,
        @Weight,
        @CarName,
        @InUse,
        0
      )

    SELECT *
    FROM   Car
    WHERE  CarID = @CarID
END

I want to know if there is a way to get selected Columns headers info in T-SQL or not? Is there any solution to get them?

For example: we can get headers of function tables by INFORMATION_SCHEMA.ROUTINE_COLUMNS table. Is there any info in sql server to get procedures returned headers?

Do you want this without actually executing the stored procedure? — Mikael Eriksson, Jun 08 '15 at 09:29
Then no, there is no way to do that. You could have dynamic SQL or if statements that will make the SP return different columns for different executions depending on parameter values or the time of day etc... — Mikael Eriksson, Jun 08 '15 at 09:31
https://msdn.microsoft.com/en-us/library/ms173839.aspx Note the deprecated warning and the alternatives. — ta.speot.is, Jun 08 '15 at 09:33
@Nima, if you are wanting to get the parameters dynamically then you can reverse engineer `sp_help` as this infers information about a stored procedure, but not the output as @Mikael described. — Kane, Jun 08 '15 at 09:33

score 2 · Answer 1 · answered Jun 08 '15 at 10:04

2

sys.dm_exec_describe_first_result_set_for_object

This dynamic management function takes an @object_id as a parameter and describes the first result metadata for the module with that ID. The @object_id specified can be the ID of a Transact-SQL stored procedure or a Transact-SQL trigger.

It only describes the first result set.

answered Jun 08 '15 at 10:04

ta.speot.is

26,914
8
68
96

could you please write an example for this? because i use sql server 2008 and this function is not supported in it. – Nima Rostami Jun 08 '15 at 10:18
Well tag your question with SQL Server 2008. – ta.speot.is Jun 08 '15 at 10:28

Best Solution to get Stored Procedure returned header Names in T-SQL

1 Answers1