如何将一个存储过程的结果作为另一个存储过程的参数传递。

第一个Store程序如下;



设置ANSI_NULLS ON

设置QUOTED_IDENTIFIER ON

GO





ALTER程序[dbo]。[Daily_SMS_Students] @session varchar（2），@ date varchar（50）

as

开始



声明@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），课程varchar（50），Batch_id varchar（50），

RoomNo varchar（20），Sess varchar（10），RoomFloor varchar（15））





begin tran

声明房间光标为

- 从co_batch_master选择cbm_batch_id，cmn_minor_code cbm_active<> 'D'和cbm_batch_start_dt = getdate（）

- 从活动房间<>中选择bthid，minor_code，RoomNo，Sess 'D'和Dateofcrs = convert（char，getdate（），101）

选择bthid，minor_code，RoomNo，来自TB_Room_Allocation_SMS的Sess，其中有效<> 'D'和Sess = @session和Dateofcrs = @date order by RoomNo



open rooms

从Rooms收到下一个@Batch_id， @ Course，@ RoomNo，@ Sess

而@@ Fetch_status = 0

begin

begin tran

declare Studdetails游标

选择s.stud_name，MobileNo = case rtrim（isnull（s.stud_mobile，''））

当''然后rtrim（s.stud_telephone）否则rtrim（s.stud_mobile）结束

来自course_registration cr，

batch_course_registration bcr，学生s cr.stud_id = s.stud_id和

bcr .cr_bill_no = cr.cr_bill_no和cr.cr_active ='A'

和s.stud_active<> 'D'和bcr.bcr_batch_id = @Batch_id



如果@RoomNo = '11'

set @RoomFloor ='1stFloor'

如果@RoomNo ='12'

设置@RoomFloor ='1stFloor'

如果@RoomNo ='13'

设置@RoomFloor ='1stFloor'

如果@RoomNo ='14'

设置@RoomFloor ='1stFloor'

如果@RoomNo = '21'

设置@RoomFloor ='2ndFloor'

如果@RoomNo = '22'

set @RoomFloor ='2ndFloor'

如果@RoomNo ='23'

设置@RoomFloor ='2ndFloor'

如果@RoomNo ='24'

设置@RoomFloor ='2ndFloor'

如果@RoomNo ='31'

设置@RoomFloor ='3rdFloor'

如果@RoomNo = '32'

设置@RoomFloor ='3rdFloor'

如果@RoomNo ='33'

set @RoomFloor ='3rdFloor'

如果@RoomNo = '34'

set @RoomFloor ='3rdFloor'

如果@RoomNo = '41'

设置@RoomFloor ='4thFloor'

如果@RoomNo ='42'

设置@RoomFloor ='4thFloor'

如果@RoomNo ='会议'

设置@RoomFloor ='底层'





打开Studdetails

从Studdetails获取下一个@ stud_name，@ Mob_num

而@@ Fetch_status = 0

开始

if（len（ltrim（rtrim（@Mob_num）））> 9）和@Mob_num<> ''和@Mob_num<> 'NULL'

开始

if（@Mob_num<>'9380244904'）

begin

insert到#TempTable值（@ stud_name，@ Mob_num，@ Course，@ Batch_id，@ RoomNo，@ Sess，@ RoomFloor）

end

end

从Studdetails获取下一个@ stud_name，@ Mob_num

结束

关闭Studdetails

deallocate Studdetails

提交转发

从Rooms收到@ Batch_id，@ Course，@ RoomNo，@ Sess

end

关闭房间

deallocate Rooms

commit tran

选择Batch_id，roomno来自#TempTable group by Batch_id，roomno

end



当我按如下方式执行第一个商店程序输出时;



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



批次房间



B10293 14

B11511 34

B11573 42

B11592 41

B11846 23

B11971 11

B12313 31

B12321 22

B180 33





我想在那个商店程序中写另一个商店程序我想要检查第一个商店程序输出上一个日期任何批处理和房间在那里使用while循环。



i想要在另一个商店程序中执行。



我该怎么办。



问候，

Narasiman P

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