如何将一个存储过程的结果作为另一个存储过程的参数传递。 [英] how to Pass One Stored Procedure’s Result as Another Stored Procedure’s Parameter.
问题描述
第一个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
推荐答案
这太糟糕了。您不想使用游标，临时表或while循环。这不是SQL的用途。 SQL用于基于集合的操作。
您可以通过编写代码来调用一个proc，然后从该proc获取值并传递给另一个。如果要传递范围，可以使用基于XML或表的变量。但是最好就这里你要做的事情进行对话，以及如何正确地进行对话。
This is just awful. You want to NOT use cursors, temp tables, or while loops. That's not what SQL is for. SQL is for set based operations.
You can do this by writing code to call one proc, then get the values from that proc and pass to the other. If you want to pass a range, you can use XML or table based variables. But it would be better to have a conversation on what you're trying to do here, and how to do it properly.
这篇关于如何将一个存储过程的结果作为另一个存储过程的参数传递。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
