SQL Server 2005 Determine Stored Procedure Output Type

Question

I'm working on a legacy system, and I need to call a stored procedure to retrieve the data I need. The problem is, I don't have any idea as to what the output column format is. Short of going into the stored procedure and figuring out the output column format from the SQL, is there a way for me to see what the output column types are? I can run the stored procedure just fine, but the code is a mess, and I'd prefer to treat it as a black box if I could.

EDIT: I know that its not possible for me to determine this from the database metadata, since the procedure may return different results based upon what the input is. I guess I should rephrase my question: given the result set from a stored procedure, how can I determine the column types?

Answer 1

what will you do if the stored proc outputs different resultsets depending on what is passed in...for example

create procedure Test
@var int
as
if @var =1
begin
select col1,col2 from table1
end
else if @var =2
begin
select col4,col2 ,col5,col1 from table2
end
else 
begin
select * from table3
end

There is a SET options but it is being deprecated

SET FMTONLY ON;
GO
exec YourProc
GO
SET FMTONLY OFF;
GO

Answer 2

As you already know, you cannot determine that information from any database metadata (since there is none) - and unfortunately, you cannot determine that from the result set, either - at least not in any reliable, deterministic way.

When you call a stored procedure, all you get back is a bunch of columns and a bunch of rows. There's no inherent information available about the types of those columns. Best you can do is guess - if the data contains alphanumeric characters, it's a VARCHAR/string field. If it has only numeric digits, and possibly a decimal separator, it's likely to be a INT or DECIMAL (or MONEY or SMALLMONEY - can't really tell for sure). If it looks like a DATE and can be converted to a DATE, it's probably a DATE, DATETIME, DATETIME2 or something like that.

The only reliable way is to have some documentation on the output values that the stored procedure generates. Anything else is guesswork at best.