问题描述
第一个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
B1029314
B1151134
B1157342
B1159241
B1184623
B1197111
B1231331
B1232122
B18033
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
推荐答案
这篇关于如何将一个存储过程的结果作为另一个存储过程的参数传递。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！