| Author |
 Topic  |
|
|
Radhiga
Starting Member
35 Posts |
 Posted - 04/11/2006 : 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
Flowing Fount of Yak Knowledge
Sweden
4110 Posts |
Posted - 04/11/2006 : 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)
Singapore
16745 Posts |
Posted - 04/11/2006 : 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
|
|
|
|
Radhiga
Starting Member
35 Posts |
Posted - 04/11/2006 : 21:01:55
|
| Thanks a lot for the Prompt answer. The repies are really very useful |
|
|
|
gautamsaraswat88
Starting Member
India
2 Posts |
Posted - 11/20/2011 : 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' |
|
|
| |
Topic |
|
how to call sp inside another sp
