Execute SPROC after completion of another SPROC

Question

Essentially, I want to do this in T-SQL

1) Execute SPROC1
2) Upon completion of SPROC1, execute SPROC2
3) Upon completion of SPROC2, execute SPROC3

I know this is possible using SSIS. If there any way I can do this?

Thanks

Answer 1

try this:

Execute SPROC1
execute SPROC2
execute SPROC3

SPROC2 will not run until SPROC1 is complete, and then SPROC3 will not run until SPROC2 is complete.

You can test it out:

CREATE PROCEDURE pTest1 AS
SELECT 1,GETDATE()
WAITFOR DELAY '00:00:02'
RETURN 0
go
CREATE PROCEDURE pTest2 AS
SELECT 2,GETDATE()
WAITFOR DELAY '00:00:02'
RETURN 0
go
CREATE PROCEDURE pTest3 AS
SELECT 3,GETDATE()
WAITFOR DELAY '00:00:02'
RETURN 0
go

EXEC pTest1
EXEC pTest2
EXEC pTest3

OUTPUT:

----------- -----------------------
1           2010-06-07 08:43:08.423

(1 row(s) affected)


----------- -----------------------
2           2010-06-07 08:43:10.423

(1 row(s) affected)


----------- -----------------------
3           2010-06-07 08:43:12.423

(1 row(s) affected)

Answer 2

For one DB call, use a wrapper

CREATE PROC SPROCwrapper
AS
EXEC SPROC1
EXEC SPROC2
EXEC SPROC3
GO

... or send 3 lines as per KM's answer.

SQL is sequentially executed