how to call sp inside another sp

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

SQL Server 2000 Forums

SQL Server Development (2000)

how to call sp inside another sp

Author

Topic

Radhiga
Starting Member

35 Posts

Posted - 2006-04-11 : 03:28:10

Hai,
1 how can i call a stored procedure inside another stored procedure...
2. in the first sp i retreiveing company_id..
say select company_id,company_name ....etc can i use the company_id as the input parameter for the second one which im calling inside this particular SP...if so, pls let me know how can i do that..
thanks

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-04-11 : 03:31:31

1 sp only

use variable to retrieve the id inside the sp, then pass that value to the query inside

or if you are going to use this id retrieval elsewhere, use function so you can incorporate it inline when being called inside an sp

HTH

--------------------
keeping it simple...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-04-11 : 05:02:15

quote:
Originally posted by Radhiga

Hai,
1 how can i call a stored procedure inside another stored procedure...
2. in the first sp i retreiveing company_id..
say select company_id,company_name ....etc can i use the company_id as the input parameter for the second one which im calling inside this particular SP...if so, pls let me know how can i do that..
thanks

1. it is the same with when you call any stored procedure

exec your_stored_procedure @para1, @para2

2. yes.

if the first sp is returning the company_id etc as a result set, you can insert the result into a temp table

create table #temp 
(    
    company_id   int,
    company_name varchar(100)
)

insert into #temp exec sp1

-- from here onwards you can access the result from sp1
select * from #temp
[/code]

Radhiga
Starting Member

35 Posts

Posted - 2006-04-11 : 21:01:55

Thanks a lot for the Prompt answer. The repies are really very useful

gautamsaraswat88
Starting Member

2 Posts

Posted - 2011-11-20 : 12:46:56

First sp
---------
alter procedure dbo.test
( @v_id varchar(10),
@p_transdate datetime output

)
as begin
--select billno,transdate,companycode,accountcode,purchbillno from a05purchasemaster where billno = @v_id
select @p_transdate = transdate from a05purchasemaster where billno = @v_id
end

-----
Second Sp

alter PROCEDURE sp_SPinSp
(
@v_col1 varchar(20)
--@ValuePassed varchar(50)
)
AS
begin
declare @v_transdate datetime
-- EXEC sp_ReturnValue @ValuePassed, @Id OUTPUT
exec test PUR0000009, @p_transdate =@v_transdate output

create table #SomeTable (col1 varchar(20), col2 datetime)
INSERT INTO #SomeTable
(col1, col2)
VALUES
(@v_col1,convert(varchar(10),@v_transdate,101))
select * from #SomeTable
end
GO

---------
to execute

exec sp_SpinSP 'gautam'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources