Stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored procedure

Author

Topic

imcurious80
Starting Member

1 Post

Posted - 2012-12-17 : 17:47:11

Hello ALL,

I am using SQL for first time.
Your help will be useful and encouraging.

Stored Proc A calls Stored Proc B using "EXEC" clause.

If the Stored proc B creates a staging Table and want to pass it for further processing to calling Proc i.e; Stored proc A.

How to do that..?

Thank you.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-12-18 : 00:21:28

See this example:


GO
CREATE procedure ProcA (@dept int)
As
	SELECT EMPLOYEE_ID, DEPARTMENT_ID, SALARY FROM EMPLOYEES WHERE DEPARTMENT_ID = @dept

--EXEC ProcA 30

GO	
CREATE  procedure ProcB 
As
BEGIN

	DECLARE @tab TABLE( empid int, dept int, sal numeric(8,2))
	INSERT INTO @tab 
	EXEC ProcA 30
	
	SELECT * FROM @tab
END

EXEC ProcB

--
Chandu

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-12-18 : 18:28:50

If ProcA calls ProcB, you can create the temp table in ProcA, call ProcB to populate the table and process the table after returning from ProcB, a la:[CODE]create proc dbo.ProcA
as
create table #MyTempA (
num int
)

exec dbo.ProcB

select *
from #MyTempA
go
create proc dbo.ProcB
as
insert into #MyTempA
values (1), (2), (3)
go

exec dbo.ProcA
go
drop proc dbo.ProcA
drop proc dbo.ProcB[/CODE]If instead you create the table inside of ProcB, it gets destroyed once ProcB goes out of scope.

=================================================
Hear the sledges with the bells - silver bells!
What a world of merriment their melody foretells!

waterduck
Aged Yak Warrior

982 Posts

Posted - 2012-12-18 : 21:12:38

you may use global temporary table
http://www.sqlteam.com/article/temporary-tables

create procedure Stored_proc_A
as
create table ##Stored_proc_A(col1 int)
insert into ##Stored_proc_A select 1

GO

create procedure Stored_proc_B
as
exec Stored_proc_A
select * from ##Stored_proc_A

GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources