0

I would like to do something similar to the following code:

WITH temp as (EXEC @return_value = [Transactions].[Myproc]
  @Id = 1)
SELECT * from temp

Is it possible in T-SQL?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Revious
  • 7,816
  • 31
  • 98
  • 147
  • possible duplicate http://stackoverflow.com/questions/19555502/using-temp-table-with-exec-sql-in-stored-procedure – Gonzalo.- May 08 '14 at 14:21
  • Possible duplicate of [stackoverflow.com/questions/6332509/call-a-stored-procedure-in-sql-cte](http://stackoverflow.com/questions/6332509/call-a-stored-procedure-in-sql-cte) – Serpiton May 08 '14 at 14:21
  • possible duplicate of http://stackoverflow.com/questions/209383/select-columns-from-result-set-of-stored-procedure – mr.Reband May 08 '14 at 14:30
  • The answer in this question is not complete and completely improveable: http://stackoverflow.com/questions/6332509/call-a-stored-procedure-in-sql-cte – Revious May 08 '14 at 14:42

1 Answers1

1

Here is an example Stored Procedure:

CREATE PROCEDURE [dbo].[sp_test]
AS
    SELECT employee,first_name,last_name FROM employee_info
GO

And here is an example utilizing the resultset returned by the procedure:

DECLARE @EmployeeInfo as Table (
employee int,
first_name varchar(40),
last_name varchar(40)
)

INSERT INTO @EmployeeInfo 
EXEC sp_test

SELECT * FROM @EmployeeInfo WHERE employee < 100 ORDER BY last_name
Besticles
  • 338
  • 2
  • 8