--Get UDT for sp results
DECLARE @cursor integer

--Create server side cursor to open procedure
EXEC sp_cursoropen @cursor output, N'exec CustOrderHist @CustomerID=1', 4, 2 

--Name cursor
EXEC sp_cursoroption @cursor, 2, 'myCursor'

--Cursor columns are stored in master..syscursorcolumns for server  cursors. type_name() returns UDT name
SELECT column_name, type_name(data_type_sql) TypeName, column_size Size
FROM master.dbo.syscursorcolumns 
WHERE cursor_handle = @cursor

--Close cursor. Retrieve no rows.
exec sp_cursorclose @cursor 

column_name  TypeName  Size        
------------- -------- ----------- 
ProductName  nvarchar  80
Total        int       4 

quote:

---

Originally posted by kselvia

Spirit1, INFORMATION_SCHEMA does not contain details on SP result sets.

--Get UDT for sp results
DECLARE @cursor integer

--Create server side cursor to open procedure
EXEC sp_cursoropen @cursor output, N'exec CustOrderHist @CustomerID=1', 4, 2 

--Name cursor
EXEC sp_cursoroption @cursor, 2, 'myCursor'

--Cursor columns are stored in master..syscursorcolumns for server  cursors. type_name() returns UDT name
SELECT column_name, type_name(data_type_sql) TypeName, column_size Size
FROM master.dbo.syscursorcolumns 
WHERE cursor_handle = @cursor

--Close cursor. Retrieve no rows.
exec sp_cursorclose @cursor 

column_name  TypeName  Size        
------------- -------- ----------- 
ProductName  nvarchar  80
Total        int       4 

--Ken
I want to die in my sleep like my grandfather, not screaming in terror like his passengers.

---

quote:

---

Originally posted by rockmoose

Nope, I have found no way to do this. And I have tried ... extensively ...

If you have a client in c#/vb etc..
The client can issue:
SET FMTONLY ON; EXEC sproc @prm1 = NULL, @prm2 = NULL
This will return formatting info to the datadrivers.
If you put this in a DataTable for example, You can query that for the MetaData [:-)]

If anyone knows how do do this in SQL - PLEASE let me know [;-)]


rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

---

C#
SqlConnection cn = new SqlConnection( ... );
SqlCommand cmd = cn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "SET FMTONLY ON;EXEC Northwind.dbo.CustOrderHist @CustomerId = NULL"; // You just need placeholders, don't need to know datatype
of param
SqlDataReader dr = cmd.ExecuteReader();
dr.Open();
DataTable dt = dr.GetSchemaTable();
dr.Close();
foreach( DataColumn dc in dt.Columns ){ /* do the interesting stuff of retrieving column metadata */ }

quote:

---

Originally posted by kselvia

Afraid it's not possible. Without executing the procedure, the result set is undefined. It could be 1 or many result sets. You can do as Rockmoose suggested and SET FMTONLY ON; and pass it placeholder paramaters with the correct datatypes.
See this post for exmaples of how to determine sp parameter types http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36396

---

quote:

---

Originally posted by rockmoose

quote:

---

Originally posted by kselvia

Afraid it's not possible. Without executing the procedure, the result set is undefined. It could be 1 or many result sets. You can do as Rockmoose suggested and SET FMTONLY ON; and pass it placeholder paramaters with the correct datatypes.
See this post for exmaples of how to determine sp parameter types http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36396

---

When using SET FMTONLY ON, you don't need to know the datatype of the parameters, just how many there are
Effectively:

SET FMTONLY ON;EXEC procW3Params NULL, NULL, NULL




rockmoose
/* Chaos is the nature of things...Order is a lesser state of chaos */

---