0

In SQL server 2012 I am looking for a way to call a Stored Procedure (A) from stored Procedure (B). SP A returns a result set that I then will need to use in SP B. Is this possible?

tbdevmanager
  • 373
  • 5
  • 15
  • http://stackoverflow.com/questions/15802511/execute-a-stored-procedure-in-another-stored-procedure-in-sql-server – Nagaraj S Aug 20 '14 at 14:03

2 Answers2

2

yes, with multiple columns too:

create table tablea
(
     cola int,
     colb varchar(50) NULL
)

GO
insert into tablea(cola, colb) values (1, 'test')
GO

CREATE PROCEDURE sp_A
AS
BEGIN

   SELECT cola, colb from tablea
END
GO

CREATE PROCEDURE sp_B
AS
BEGIN

 Declare @TempTable Table (col1 int, col2 varchar(50) NULL)

 Insert @TempTable Exec sp_A

 SELECT * from @TempTable

END

GO

exec sp_B
Donal
  • 31,121
  • 10
  • 63
  • 72
  • I need to return a result set to b that has multiple rows that need to be used in B – tbdevmanager Aug 20 '14 at 14:25
  • @TimBrown ok, the above will work with multiple columns. – Donal Aug 20 '14 at 14:40
  • I am still getting the error 'An INSERT EXEC statement cannot be nested.' – tbdevmanager Aug 20 '14 at 14:40
  • ok, it looks like you are doing a number of insert execs in your stored procedure chain - see here: http://stackoverflow.com/questions/3795263/errors-insert-exec-statement-cannot-be-nested-and-cannot-use-the-rollback-s – Donal Aug 20 '14 at 14:42
  • I think you should look at creating a table and inserting and selecting from that table in your different procedures. – Donal Aug 20 '14 at 14:43
1

You can create a temp table / table variable / real table and insert the results into it.

Insert Into MyTable
EXEC MySP
Steve
  • 5,585
  • 2
  • 18
  • 32
  • When I try this I get the error "An INSERT EXEC statement cannot be nested." – tbdevmanager Aug 20 '14 at 14:23
  • I use this all the time without any issues. Are you only returning results for 1 table using something like `SELECT * FROM {Table}`? Or are you using a RETURN ?? statement in your SP (A)? – Steve Aug 20 '14 at 16:09