Structure of SP results
-
Can we get the structure (column name & length) of records returned by SP ?
-
No directly from sql server (unless using DTS). Here's what I would do :
IF EXISTS (SELECT * FROM dbo.SysObjects WHERE Name = 'DemoFMTONLY' AND XType = 'P')
DROP PROCEDURE dbo.DemoFMTONLY
GO
CREATE PROCEDURE dbo.DemoFMTONLY
AS
SET NOCOUNT ON
SELECT Name, Id FROM dbo.SysObjects WHERE XType = 'U'
SET NOCOUNT OFF
GO
SET FMTONLY ON --returns only the structure of the data
Go
--call this using ADO.
EXEC dbo.DemoFMTONLY
--then loop through all fields and fetch the datatype and lenght in the properties collection (iirc)
GO
SET FMTONLY OFF
GO
DROP PROCEDURE dbo.DemoFMTONLY
-
Try looking at table "syscolumns". It includes not just table columns but also stored procedure columns
.For example:
SELECT *
FROM syscolumns WITH (NOLOCK)
WHERE id = OBJECT_ID(N'storedProcName')
ORDER BY colid
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
-
It contains list of parameters, not columns from returning recordset.
Database just cannot contain this information.
SP may return different results depending on the values of parameters, it may return more than 1 recordset.
List of columns for which one you would prefer to see?
_____________
Code for TallyGenerator -
Sorry, correct, I mis-read the original q.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
-
My purpose is.. I want to create a table from the record returned by SP. The SP is containing dynamic SQL, so it execute different SQL depends on the parameter.
example:
exec SPExecSQL 'Select IDCustomer,dateConfirm,ItemCode,ItemQty'
inside the SP I have to make another process and then save the result to table..
-
So you're building some sort of reporting tool?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply