SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to get column names from stored procedure on M
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

WarrenW
Starting Member

USA
19 Posts

Posted - 04/02/2013 :  16:37:37  Show Profile  Reply with Quote
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

USA
5072 Posts

Posted - 04/02/2013 :  16:43:54  Show Profile  Visit russell's Homepage  Reply with Quote
Saw this? http://msdn.microsoft.com/en-us/library/ff878602.aspx
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 04/02/2013 :  16:50:48  Show Profile  Reply with Quote
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.

Edited by - James K on 04/02/2013 16:51:36
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/02/2013 :  21:04:38  Show Profile  Visit russell's Homepage  Reply with Quote
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

USA
19 Posts

Posted - 04/03/2013 :  00:28:33  Show Profile  Reply with Quote
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

USA
19 Posts

Posted - 04/03/2013 :  00:41:00  Show Profile  Reply with Quote
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'

Edited by - WarrenW on 04/03/2013 00:41:59
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/03/2013 :  01:10:23  Show Profile  Visit russell's Homepage  Reply with Quote
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

USA
19 Posts

Posted - 04/03/2013 :  09:41:51  Show Profile  Reply with Quote
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

USA
19 Posts

Posted - 04/03/2013 :  09:57:14  Show Profile  Reply with Quote
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.

Edited by - WarrenW on 04/03/2013 09:58:52
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 04/03/2013 :  10:02:14  Show Profile  Visit russell's Homepage  Reply with Quote
Just create a separate stored procedure that returns the availablle fields from which they can choose.
Go to Top of Page

WarrenW
Starting Member

USA
19 Posts

Posted - 04/03/2013 :  11:00:25  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000