Execute store procedure like a "table" for SELECT operator (MS SQL SERVER)

Question

Is it possible to execute store procedure like a "table" for SELECT operator (MS SQL SERVER)?

Something like

SELECT TotalSum FROM exec MyStoreProcedure '2011/11/01', '2011/11/01'

I mean somehow integrate it into the SELECT operator?

Thank you!

Thanks guys!

The solution what I did is based on your answers:

declare @result table (f1 varchar(20),f2 varchar(20), CodProducto int, NomProducto varchar(1000), Costo decimal, Cantidat int, Total decimal)
INSERT INTO @result exec  MyStoreProcedure '20111201', '20111201'
select * from @result

Nope, but you can insert those results on a table (if you know the columns of the result dataset of your sp, of course) — Lamak
– Lamak, Commented Mar 29, 2012 at 12:24

Diego · Accepted Answer · 2012-03-29 12:26:52Z

22

I supposed your proc returns several columns and you just want one, right?

small workaround is to add the result of the proc to a table variable and then select from it

create proc proc1 as
select 1 as one, 2 as two

declare @result table (one int, two int)

insert into @result
exec proc1

select one from @result

answered Mar 29, 2012 at 12:26

Diego

36.5k21 gold badges94 silver badges138 bronze badges

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

1 Comment

NoWar Over a year ago

Question: Is it possible to put it into SQLCommand object of .NET? Will do it work?

Vinnie · Accepted Answer · 2012-03-29 12:29:04Z

5

This would be better as a function rather than a stored procedure.

create function dbo.TestTable
(@var1 bit)
returns table
AS
RETURN
( select *
    from INFORMATION_SCHEMA.TABLES
    where @var1 = 1
);


select * from
dbo.TestTable(1)

answered Mar 29, 2012 at 12:29

Vinnie

3,9471 gold badge28 silver badges30 bronze badges

Comments

Mitch Wheat · Accepted Answer · 2012-03-29 12:24:03Z

2

Not directly (or without altering the stored procedure to be a table-valued function).

But you could do this:

INSERT INTO SomeTempTableWithSchemaMatchingTheSproc (...)
EXEC MyStoredProcedure 

SELECT * FROM SomeTempTableWithSchemaMatchingTheSproc

SQL Server 2005 onwards, you can also use a table variable.

answered Mar 29, 2012 at 12:24

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Comments

ekhumoro · Accepted Answer · 2017-11-29 02:30:24Z

1

This works for me:

CREATE VIEW dbo.vw_xxx
AS
  select * from openquery(YOURSERVERNAME, 'exec [sp_xxx] '''','''','''','''','''','''' ')

edited Nov 29, 2017 at 2:30

ekhumoro

122k23 gold badges272 silver badges400 bronze badges

answered Nov 29, 2017 at 2:08

deni

111 bronze badge

Collectives™ on Stack Overflow

Execute store procedure like a "table" for SELECT operator (MS SQL SERVER)

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related