sp_sproc_columns doesn't return result columns

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

Answer 1

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

Answer 2

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