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.
| Author |
Topic |
|
SoftFox
Starting Member
42 Posts |
 Posted - 2008-01-24 : 06:47:33
|
| Is it possible to create a function or similar that selects specific fields(or even just all fields) from a stored procedure that selects a data set.E.g if i have an SP called GetData that returns the following:Field1 Field2 Field31 a x2 b y3 c zI would like a function something like:select * from dbo.fnGetSPFields("GetData", "Field2, Field3)that would return:Field2 Field3a xb yc zIs anything like this possible? Or even if it would just select all the fields from the SP this would be very usefull. |
|
|
mahesh_bote
Constraint Violating Yak Guru
298 Posts |
Posted - 2008-01-24 : 06:59:56
|
| better you write SProc in such a way so that it will return that much of cols.if u r concern only with Field2, Field3, then just write ... Select Field2, Field3 From .....Thanks,Mahesh |
 |
|
|
SoftFox
Starting Member
42 Posts |
Posted - 2008-01-24 : 08:32:28
|
| What i am hoping to be able to do here is to "SELECT" from an SP as if it was a table or a view. I know that it is possible to execute an SP into a table/temp table and then select from that, but here i have SPs that return 300 or more fields, of which i only want to select a few. So it would be very usefull if it was possible to select from an SP without having to create a table first with the exact number of fields all defined as the correct datatypes as the SP returns. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-01-24 : 09:03:17
|
quote:
Originally posted by SoftFox
What i am hoping to be able to do here is to "SELECT" from an SP as if it was a table or a view. I know that it is possible to execute an SP into a table/temp table and then select from that, but here i have SPs that return 300 or more fields, of which i only want to select a few. So it would be very usefull if it was possible to select from an SP without having to create a table first with the exact number of fields all defined as the correct datatypes as the SP returns.
Refer method 2http://sqlblogcasts.com/blogs/madhivanan/archive/2007/11/26/select-columns-from-exec-procedure-name-is-this-possible.aspxMadhivananFailing to plan is Planning to fail |
|
|
|
SoftFox
Starting Member
42 Posts |
Posted - 2008-01-24 : 10:26:32
|
| Thank you for the article very useful - I'd just found my way to this article from a different thread! OPENROWSET does seem to be the answer. There are a couple of issues ive had using this in case you know the solution to them:1.When i altered an SP slightly so that it returns different result sets depending on a parameter passed in, the OPENROWSET threw an error:"Could not process object 'execute [headway development control].dbo.PCLetterFetchMasterProc 0'. The OLE DB provider 'SQLOLEDB' indicates that the object has no columns."- despite the fact that the SP definetly returns columns. Is there something i need to do to refresh/update sys tables or similar so it knows that the SP does in fact return columns?2.If the SP returns 2 datasets, is there a way of making this work to just return the first dataset? It throws an error for me if i try to run it on an SP returning 2 datasets.3.When i tried it on a different server it says it is not configured correctly:"SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure.."Does anyone know how exacly you do configure this? |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2008-01-25 : 11:05:49
|
| Make sure all your calculated columns have proper alias namesMadhivananFailing to plan is Planning to fail |
|
|
|
SoftFox
Starting Member
42 Posts |
Posted - 2008-01-29 : 10:16:14
|
| For anyone interested, I have solved issues nos 1 and 2 by adding "SET FMTONLY OFF" to the execute statement as below:Select AgentName,ApplicantName,ApplicationNumber from OPENROWSET('SQLOLEDB','Data Source=LGS-SQL2005;Trusted_Connection=yes;Integrated Security = SSPI','SET FMTONLY OFF; execute [headway development control].dbo.PCLetterFetchMasterProc 1244').Issue no 3 above configuring the server i have still to solve.. |
|
|
|
|
|
|
|
|
function to select fields from stored procedure