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 |
|
|
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.aspxEditing: Sorry russell, didn't see your reply before I hit "post" button. |
|
|
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 |
|
|
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. |
|
|
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!WarrenSELECT parm.name AS Parameter, typ.name AS [Type]FROM sys.procedures spJOIN sys.parameters parm ON sp.object_id = parm.object_idJOIN sys.types typ ON parm.system_type_id = typ.system_type_idWHERE sp.name = 'sproc name here' |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
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. |
|
|
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 |
|
|
|