Return stored procedure result in function

Question

I have a complex stored procedure whose results I need to retrieve in a view somehow. The end user is requesting the results to be linked in an Excel spreadsheet and I can only link views and tables in Excel.

I know you can fudge your way through this somehow using openquery but it seems like that is not a desirable way of going about this and even when I tested this, I got errors in return.

I can always dump the results into a table that's occasionally refreshed but the user is pretty intent on having this data live on demand.

The other way seems to be through a function. I tried this but no matter how I slice it I can't seem to get the function to accept a stored procedure.

CREATE FUNCTION f_testFunction
(
    @Parameter INT
)
RETURNS TABLE
AS
RETURN
(
    EXEC sp_testProdedure @Parameter
)

GO

Is something like the above even possible?

Community · Accepted Answer · 2017-04-13 12:42:36Z

9

No, you can't EXEC within a function. Well, there is one workaround (you mentioned it), but I don't recommend it:

https://dba.stackexchange.com/a/12745/1186

So you should consider moving your complex logic from a procedure to a table-valued function, then both your view and the procedure could reference that, you still only have one version to maintain, and everyone is happy...

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Nov 14, 2013 at 19:16

Aaron Bertrand

282k37 gold badges469 silver badges468 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Tom Over a year ago

Can I use all stored procedure logic in a table-valued function? i.e. case, if, etc? I assume I can then select from this function in a view which I can then link to Excel correct?

Aaron Bertrand Over a year ago

You can use a lot, but not all (e.g. if your stored procedure uses EXEC, you can't port that directly). Also there is a big difference between a multi-statement function (which would have an explicit table declaration, IF, etc.) and an inline table-valued function (which just says RETURN (this result set). The latter is going to perform extremely well, the former will not. And yes, there shouldn't be any roadblocks to wrapping CREATE VIEW AS around a SELECT from a table-valued function of either flavor.

Aaron Bertrand Over a year ago

By cache table do you mean a #temp table? If so, you might start thinking about doing it all in a single query rather than staging data and working on it...

Aaron Bertrand Over a year ago

If you show the logic of your stored procedure (in a new question, or I suppose you could update this one since I'm the only one with a real answer so far), anonymizing some of it if you have to, someone will be happy to help you convert that to a table-valued function (hopefully an inline one).

Aaron Bertrand Over a year ago

@Tom yeah, that's a little excessive. You might want to ask for free help converting something similar but a lot smaller, and taking what you learn from that and applying it to your much larger procedure.

|

Gary Walker · Accepted Answer · 2013-11-14 20:00:36Z

1

Often you can rewrite a stored proc as a table valued function, then it is a function - problem solved.

answered Nov 14, 2013 at 20:00

Gary Walker

9,2043 gold badges22 silver badges43 bronze badges

Collectives™ on Stack Overflow

Return stored procedure result in function

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related