SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 how to call sp inside another sp
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Radhiga
Starting Member

35 Posts

Posted - 04/11/2006 :  03:28:10  Show Profile  Reply with Quote
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  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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...
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 04/11/2006 :  05:02:15  Show Profile  Reply with Quote
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
Go to Top of Page

Radhiga
Starting Member

35 Posts

Posted - 04/11/2006 :  21:01:55  Show Profile  Reply with Quote
Thanks a lot for the Prompt answer. The repies are really very useful
Go to Top of Page

gautamsaraswat88
Starting Member

India
2 Posts

Posted - 11/20/2011 :  12:46:56  Show Profile  Reply with Quote
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'
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000