SELECT from stored procedure inside a view

Question

Can someone enlighten me as to why this will not work?

I have a stored procedure that returns a table: calling it like so works fine:

EXECUTE dbo.sp_Get_Total_Parcels_Paid_Data

However, I need to use this inside of a view, and cannot get it to work.

Select * from dbo.sp_Get_Total_Parcels_Paid_Data()

Error:

Invalid object name 'dbo.sp_Get_Total_Parcels_Paid_Data()

SP Code:

create PROCEDURE [dbo].[sp_Get_Total_Parcels_Paid_Data]
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

declare @Sql1 nvarchar(max)
,@Sql2 nvarchar(max)
,@Sql3 nvarchar(max)

declare @TallyResults table
(EFOLDERID nvarchar(31) null    
,inv_bank_cd int null
,inv_cd int null
,inv_group_cd int null
,intLoanNumber int null
,other_text nvarchar(250) null
,tallyCount int
,eidMgrId nvarchar(31))


select @Sql1 = 'Select ' + tallydefinition from jobfunctions where jobfuncid = 44
select @Sql2 = 'Select ' + tallydefinition from jobfunctions where jobfuncid = 45
select @Sql3 = 'Select ' + tallydefinition from jobfunctions where jobfuncid = 206

insert into @TallyResults exec sp_executesql @Sql1
insert into @TallyResults exec sp_executesql @Sql2
insert into @TallyResults exec sp_executesql @Sql3

select * from @TallyResults

END

this looks like something you could roll into a view without having to do the gnarly multiple select statements that insert into a table variable. this looks like an optimization nightmare. is `tallydefinition` a column? your dynamic sql in the stored procedure looks like it might throw an error. — swasheck, Mar 22 '13 at 19:25
Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://sqlserverpedia.com/blog/sql-server-bloggers/stored-procedure-performance-using-%E2%80%9Csp_%E2%80%9D-prefix-%E2%80%93-myth-or-fact/). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! — marc_s, Mar 22 '13 at 20:10
@marc_s [I revisited those tests recently too](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). — Aaron Bertrand, Mar 22 '13 at 20:13

score 5 · Answer 1 · edited Apr 13 '17 at 12:42

5

You can't SELECT FROM <procedure>, sorry. Maybe you can try the OPENQUERY trick, but I'm not sure you'll be able to put that in a view.

edited Apr 13 '17 at 12:42

Community

1
1

answered Mar 22 '13 at 19:20

Aaron Bertrand

272,866
37
466
490

score 0 · Answer 2 · edited May 23 '17 at 10:30

0

We can't execute a Stored Procedure in a Select statement. Here's a similar thread

SQL Server - SELECT FROM stored procedure

edited May 23 '17 at 10:30

Community

1
1

answered Mar 22 '13 at 19:20

Narsimha

184
4

score 0 · Answer 3 · answered Mar 22 '13 at 19:30

0

If I am not wrong this is what you want

Perhaps, this will guide you the ways in which you can get the result set from one SP to another.

answered Mar 22 '13 at 19:30

Zerotoinfinity

6,290
32
130
206

SELECT from stored procedure inside a view

3 Answers3