locked
sp_sproc_columns doesn't return result columns RRS feed

  • Question

  • I just tried running sp_sproc_columns against some of my sp's and I only get the parameters and the return value columns, not the result columns (type 3 = SQL_Result_Col).  I'm running SQL 2008 Dev Edition.  Also, I tried running against some system sps (such as sp_sproc_columns sp_sproc_columns ).  It still doesn't work.  Can anyone tell me how to fix this?

    Thanks in advance,

    Terry
    Thursday, January 14, 2010 10:07 PM

Answers

  • Terry,

    sp_sproc_columns returns only the input parms. To get the output column structure for a stored procedure, check out the following example. Let us know if helpful.

     

    -- T-SQL: how to get stored procedures output columns info
    SELECT TOP (0) * INTO OutputOfuspGetBillOfMaterials
    FROM OPENQUERY(SRVNAME, 'exec AdventureWorks.dbo.uspGetBillOfMaterials ''800'',''2004-01-01''')
    GO
    exec sp_help OutputOfuspGetBillOfMaterials
    /*
    Column_name	Type	Computed	Length
    ProductAssemblyID	int	no	4
    ComponentID	int	no	4
    ComponentDesc	nvarchar	no	100
    TotalQuantity	numeric	no	17
    StandardCost	money	no	8
    ListPrice	money	no	8
    BOMLevel	smallint	no	2
    RecursionLevel	int	no	4
    */

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Proposed as answer by Naomi N Friday, January 15, 2010 7:46 AM
    • Marked as answer by Zongqing Li Thursday, January 21, 2010 5:47 AM
    Thursday, January 14, 2010 10:31 PM

All replies

  • Terry,

    sp_sproc_columns returns only the input parms. To get the output column structure for a stored procedure, check out the following example. Let us know if helpful.

     

    -- T-SQL: how to get stored procedures output columns info
    SELECT TOP (0) * INTO OutputOfuspGetBillOfMaterials
    FROM OPENQUERY(SRVNAME, 'exec AdventureWorks.dbo.uspGetBillOfMaterials ''800'',''2004-01-01''')
    GO
    exec sp_help OutputOfuspGetBillOfMaterials
    /*
    Column_name	Type	Computed	Length
    ProductAssemblyID	int	no	4
    ComponentID	int	no	4
    ComponentDesc	nvarchar	no	100
    TotalQuantity	numeric	no	17
    StandardCost	money	no	8
    ListPrice	money	no	8
    BOMLevel	smallint	no	2
    RecursionLevel	int	no	4
    */

    Kalman Toth, SQL Server & BI Training, SSAS, SSIS, SSRS; http://www.SQLUSA.com
    • Proposed as answer by Naomi N Friday, January 15, 2010 7:46 AM
    • Marked as answer by Zongqing Li Thursday, January 21, 2010 5:47 AM
    Thursday, January 14, 2010 10:31 PM
  • Just to expand a bit on Kalman's answer:

    What a procedure returns is not avaibale as meta-data, since it isn't known. You can have conditional logic inside the proc, do dynamic SQL etc. Such thing can change what tables and what columns you SELECT from. So, the only way to get to such meta-data is to actually execute something, as in Kalman's example. Some tools uses some SET FTMONLY trick, but that isn't fully reliable either, since we can't know whatever conditional logic might exist in the proc.
    Tibor Karaszi, SQL Server MVP | http://www.karaszi.com/sqlserver/default.asp | http://sqlblog.com/blogs/tibor_karaszi
    • Proposed as answer by Naomi N Friday, January 15, 2010 7:46 AM
    Friday, January 15, 2010 7:45 AM