you need begin and end wrapped around the function body (after the as)
Ray
2010-01-11 21:06:48
views:
275answers:
3
+1
A:
No matter what you do, you will not be able to transform a procedure into a table valued function nor into a view. There are severe restriction around what a function is allowed to do and what not. Because a TVF or a view can be combined with any other statement, like appear in a SELECT as a subquery, or be part of an UPDATE/INSERT/DELETE and so on and so forth, there are certain behavior restrictions imposed on functions, specially when in comes to execution side effects. Procedure son the other hand are free as a bird.
The only thing you can do is to capture the output of a procedure into a table, using INSERT ... EXEC... and that's it, with the extra added restrictions that there cannot be another INSERT ... EXEC ... nested.
Eeverything you posted points that fetchCcoverageByState_V2 must be a table valued function, and that is the only avenue you should pursue.
Remus Rusanu
2010-01-11 21:07:47
+1
A:
Try using OPENROWSET, as in this question. I thought I had blogged about it some time, but perhaps not.
http://stackoverflow.com/questions/653714/how-to-select-into-temp-table-from-stored-procedure
Remember to upvote Aaron's answer.
Rob Farley
2010-01-11 21:22:41
Thanks Rob. Can you help with this syntax I've tried from Aaron's example:SELECT *FROM OPENROWSET('SQLOLEDB', 'Server=(local)\johna;Trusted_Connection=yes;', 'EXECUTE dbo.FetchCoverageByState_V2')I get error: "SQL Server does not exist or access denied".All the examples seem to be for linked remote servers or for MS Access. Thanks.
John Galt
2010-01-12 23:58:22
I don't have access to my scripts this week (I'm on my phone only). But there's an example in my scripts at http://msmvps.com/blogs/robfarley/archive/2008/09/03/dat283.aspx
Rob Farley
2010-01-13 04:57:50
It might be the brackets. Try using localhost instead? Is johna the name of the instance?
Rob Farley
2010-01-13 04:59:01
Your script example was helpful. I removed johna (which was my userid) and followed your syntax. I got a new error "Could not find stored procedure dbo.FetchCoverageByState_V2". Then I realized I had to expand to a 3part name so I prefixed dbo with the name of the database and it worked. You are great! Thanks for the help. I think I read somewhere that this technique would not be needed any longer if I could upgrade to SQL2005. This works for now. Thanks so much.
John Galt
2010-01-13 18:03:45
If you want to use the results of a stored procedure as a table expression, you'll still need to do it this way in later versions of SQL.
Rob Farley
2010-01-13 22:13:14