Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get column names from stored procedure on M

Author  Topic 

WarrenW
Starting Member

20 Posts

Posted - 2013-04-02 : 16:37:37
Hello,

I need to retrieve a list of columns returned from a stored procedure on MS SQL which may be 2005 or higher. I also need to do this without passing any input parameters even if they are required. I had read about using the SET FMTONLY ON but I still have to pass the parameters. I just need to select the column names so they can be used for generating a report. The user will select a stored procedure which I can already get a list of but I want to select one and see the columns returned.

Thanks!

Warren

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-02 : 16:43:54
Saw this? http://msdn.microsoft.com/en-us/library/ff878602.aspx
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-04-02 : 16:50:48
Because the columns returned from the stored proc can depend on the parameters passed, prior to SQL 2012, you would need to pass the parameters in order to get the column headers. If you are on SQL 2012, you could try sys.dm_exec_describe_first_result_set_for_object : http://msdn.microsoft.com/en-us/library/ff878236.aspx

Editing: Sorry russell, didn't see your reply before I hit "post" button.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-02 : 21:04:38
All good James

That's what we call being sniped around here and share one of these


Go to Top of Page

WarrenW
Starting Member

20 Posts

Posted - 2013-04-03 : 00:28:33
Thanks. I'm writing an app that may be used with 2012 or 2008. I wish there was some way of executing the stored procedure with the parameters as null. I don't care for the data returned, only the columns returned. I'm wanting the user to select the columns of data to include in a table. I guess as last resort I can prompt the user to enter data for the parameters. Is there a way to get a list of parameters for the stored procedure? I will try searching on this now.
Go to Top of Page

WarrenW
Starting Member

20 Posts

Posted - 2013-04-03 : 00:41:00
Okay, I found a routine shown below that will list the parameters for a stored procedure. So now what do I do to get the columns only that are returned and no data? Should I set the FMTONLY to ON and call the stored procedure? I believe that causes it not to return any data. How do I extract the column headers from the empty recordset?

Thanks!

Warren


SELECT parm.name AS Parameter,
typ.name AS [Type]
FROM sys.procedures sp
JOIN sys.parameters parm ON sp.object_id = parm.object_id
JOIN sys.types typ ON parm.system_type_id = typ.system_type_id
WHERE sp.name = 'sproc name here'
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-03 : 01:10:23
This all sounds like a bad idea. How 'bout you tell us what you really need to accomplish and we help you figure out a solution.
Go to Top of Page

WarrenW
Starting Member

20 Posts

Posted - 2013-04-03 : 09:41:51
I am trying to get a list of the columns returned from the stored procedure. I am then going to let the user select which ones they want to be shown in a table for a report. I am writing my own dashboard app for someone and would like for them to use existing stored procedures.
Go to Top of Page

WarrenW
Starting Member

20 Posts

Posted - 2013-04-03 : 09:57:14
And I was hoping to have this work for 2005, 2008 and 2012. The SET FMTONLY is said not to work in 2012. I was hoping to use one routine that would work for all of these versions.

I guess so far my only solution is to have the user enter any input parameters for the stored procedure and have it run the sproc and I can retrieve them from the asp.net page I am doing this from.
Go to Top of Page

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-04-03 : 10:02:14
Just create a separate stored procedure that returns the availablle fields from which they can choose.
Go to Top of Page

WarrenW
Starting Member

20 Posts

Posted - 2013-04-03 : 11:00:25
They are selecting the stored procedure from a list of all on their server. So I can't do that. And I'm trying to do this without creating new stored procedures. I can call SQL to get info but I'm not going to create a new one. So just running the selected sproc to get the column names is the only way.

Thanks
Go to Top of Page
   

- Advertisement -