how to Pass One Stored Procedure’s Result as Another Stored Procedure’s Parameter.

See more:

SQL-Server

First Storeprocedure as follows;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO


ALTER procedure [dbo].[Daily_SMS_Students] @session varchar(2), @date varchar(50)
as
begin

declare @stud_name varchar(100),
@Mob_num varchar(15),
@Course varchar(50),
@Batch_id varchar(20),
@RoomNo varchar(20),
@Sess varchar(10),
@RoomFloor varchar(15)

create table #TempTable(stud_name varchar(100),Mob_num varchar(15),Course varchar(50),Batch_id varchar(50),
RoomNo varchar(20),Sess varchar(10),RoomFloor varchar(15))


begin tran
declare Rooms cursor for
-- select cbm_batch_id, cmn_minor_code from co_batch_master where cbm_active <> 'D' and cbm_batch_start_dt = getdate()
-- select bthid,minor_code,RoomNo,Sess from room where active <> 'D' and Dateofcrs = convert(char,getdate(),101)
select bthid,minor_code,RoomNo,Sess from TB_Room_Allocation_SMS where active <> 'D' and Sess = @session and Dateofcrs = @date order by RoomNo

open Rooms
fetch next from Rooms into @Batch_id,@Course,@RoomNo,@Sess
While @@Fetch_status = 0
begin
begin tran
declare Studdetails cursor for
select s.stud_name,MobileNo = case rtrim(isnull(s.stud_mobile,''))
when '' then rtrim(s.stud_telephone) else rtrim(s.stud_mobile) end
from course_registration cr,
batch_course_registration bcr, student s where cr.stud_id = s.stud_id and
bcr.cr_bill_no = cr.cr_bill_no and cr.cr_active = 'A'
and s.stud_active <> 'D' and bcr.bcr_batch_id = @Batch_id

if @RoomNo = '11'
set @RoomFloor = '1stFloor'
if @RoomNo = '12'
set @RoomFloor = '1stFloor'
if @RoomNo = '13'
set @RoomFloor = '1stFloor'
if @RoomNo = '14'
set @RoomFloor = '1stFloor'
if @RoomNo = '21'
set @RoomFloor = '2ndFloor'
if @RoomNo = '22'
set @RoomFloor = '2ndFloor'
if @RoomNo = '23'
set @RoomFloor = '2ndFloor'
if @RoomNo = '24'
set @RoomFloor = '2ndFloor'
if @RoomNo = '31'
set @RoomFloor = '3rdFloor'
if @RoomNo = '32'
set @RoomFloor = '3rdFloor'
if @RoomNo = '33'
set @RoomFloor = '3rdFloor'
if @RoomNo = '34'
set @RoomFloor = '3rdFloor'
if @RoomNo = '41'
set @RoomFloor = '4thFloor'
if @RoomNo = '42'
set @RoomFloor = '4thFloor'
if @RoomNo = 'Conference'
Set @RoomFloor = 'Ground Floor'


open Studdetails
fetch next from Studdetails into @stud_name,@Mob_num
while @@Fetch_status = 0
begin
if (len(ltrim(rtrim(@Mob_num))) > 9) and @Mob_num <> '' and @Mob_num <> 'NULL'
begin
if (@Mob_num <> '9380244904')
begin
insert into #TempTable values(@stud_name,@Mob_num,@Course,@Batch_id,@RoomNo,@Sess,@RoomFloor)
end
end
fetch next from Studdetails into @stud_name,@Mob_num
end
close Studdetails
deallocate Studdetails
commit tran
fetch next from Rooms into @Batch_id,@Course,@RoomNo,@Sess
end
close Rooms
deallocate Rooms
commit tran
select Batch_id, roomno from #TempTable group by Batch_id, roomno
end

When i execute the first store procedure output as follows;

exec Daily_SMS_Students 'PM','2013-05-02'

Batchid room

B10293 14
B11511 34
B11573 42
B11592 41
B11846 23
B11971 11
B12313 31
B12321 22
B180 33


I want to write another store procedure in that store procedure i want to check for the first store procedure output previous date any Batchid and room is there using while loop.

i want to execute in another store procedure.

for that how can i do.

Regards,
Narasiman P