如何在商店程序中将日期作为参数传递 [英] how to pass the between date as parameter in store procedure
问题描述
我的商店程序如下
设置ANSI_NULLS ON
设置QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo]。[Presea_Intakerpts]
as
begin
设置nocount
声明@BatchID varchar（20），@ MajorName varchar（100），@ Minorcode varchar（10），@ Intake_Capacity int ，
@BatchStartdate varchar（15），@ BatchEnddate varchar（15）
创建表#TempTable（MajorName varchar（100），Minorcode varchar（10），BatchStartdate varchar（15），BatchEnddate varchar（15），
Intake_Capacity varchar（5））
声明Batch_details_Cur光标for
选择cbm.cbm_batch_id，Course_Name =（从co_minor_master中选择前1 ltrim（cmn_Minor_desc），其中cmn_minor_Code = cbm.cmn_minor_code），cbm.cmn_minor_Code，
Intake_Capacity =（从co_batch_number中选择前1位cbn_batch_nos，其中cbm_batch_id = cbm.cbm_batch_ id），
将（char（12），cbm.cbm_batch_start_dt，106）转换为BatchStartdate，将（char（12），cbm.cbm_batch_end_dt，106）转换为BatchEnddate
来自co_batch_master cbm，其中cbm.cbm_active<> 'D'和datepart（年，cbm_batch_start_dt）<> '3000'
和datepart（年，cbm_batch_start_dt）<> '4000'和datepart（年，cbm_batch_start_dt）<> '5000'和datepart（年，cbm_batch_start_dt）<> '6000'
和cbm.cmj_major_code ='pstf'
和（（'03 03/04'和'03 / 15/2014'之间的cbm_batch_start_dt）或
（cbm_batch_end_dt介于'03 / 03/2014'和'03 / 15/2014'））
订购cbm.cmn_minor_Code，cbm.cbm_batch_start_dt
打开Batch_details_Cur
从Batch_details_Cur获取下一个@BatchID，@ MajorName，@ Mincode，@ Intake_Capacity，@ StarStartdate，@ BatchEnddate
而@@ Fetch_status = 0
开始
打印@Intake_Capacity
插入#TempTable值（@ MajorName，@ Minorcode，@ BatchStartdate，@ BatchEnddate，@ Intake_Capacity）
从Batch_details_Cur获取下一个到@BatchID，@ MajorName，@ Mincode ，@ Intake_Capacity，@ BatchStartdate，@ BatchEnddate
end
close Batch_details_Cur
Deallocate Batch_details_Cur
从#TempT中选择*能够
结束
当我执行商店程序输出时如下
次要代码BatchStartdate BatchEnddate Intake_Capacity MajorName
DME 3-Mar-14 3-Mar-14 6 PSTF
GME 3-Mar-14 5-Mar-14 9 PSTF
查询如下< br $> b $ b
选择cbm.cbm_batch_id，Course_Name =（从co_minor_master中选择top 1 ltrim（cmn_Minor_desc），其中cmn_minor_Code = cbm.cmn_minor_code），cbm。 cmn_minor_Code，
Intake_Capacity =（从co_batch_number中选择前1个cbn_batch_nos，其中cbm_batch_id = cbm.cbm_batch_id），
将（char（12），cbm.cbm_batch_start_dt，106）转换为BatchStartdate ，转换（char（12），cbm.cbm_batch_end_dt，106）作为BatchEnddate
来自co_batch_master cbm，其中cbm.cbm_active<> 'D'和datepart（年，cbm_batch_start_dt）<> '3000'
和datepart（年，cbm_batch_start_dt）<> '4000'和datepart（年，cbm_batch_start_dt）<> '5000'和datepart（年，cbm_batch_start_dt）<> '6000'
和cbm.cmj_major_code ='pstf'
和（（'03 03/04'和'03 / 15/2014'之间的cbm_batch_start_dt）或
（cbm_batch_end_dt介于'03 / 03/2014'和'03 / 15/2014'））
订购cbm.cmn_minor_Code，cbm.cbm_batch_start_dt
$ />
在上面的查询中我手动给出日期批量startdt和批次enddt如下
（ （'03 / 03/2014'和'03 / 15/2014'之间的cbm_batch_start_dt）或
（'03 / 03/2014'和'03 / 15/2014'之间的cbm_batch_end_dt）） />
由cbm.cmn_minor_Code订购，cbm.cbm_batch_start_dt
i想在我上面的商店程序中将日期设置为参数。
我该怎么办。
问候，
narasiman P.
My store procedure as follows
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[Presea_Intakerpts]
as
begin
set nocount on
declare @BatchID varchar(20), @MajorName varchar(100), @Minorcode varchar(10), @Intake_Capacity int,
@BatchStartdate varchar(15), @BatchEnddate varchar(15)
Create table #TempTable (MajorName varchar(100), Minorcode varchar(10), BatchStartdate varchar(15), BatchEnddate varchar(15),
Intake_Capacity varchar(5))
Declare Batch_details_Cur Cursor for
select cbm.cbm_batch_id, Course_Name = (select top 1 ltrim(cmn_Minor_desc) from co_minor_master where cmn_minor_Code = cbm.cmn_minor_code), cbm.cmn_minor_Code,
Intake_Capacity = (select top 1 cbn_batch_nos from co_batch_number where cbm_batch_id=cbm.cbm_batch_id),
convert(char(12), cbm.cbm_batch_start_dt,106) as BatchStartdate, convert(char(12),cbm.cbm_batch_end_dt,106) as BatchEnddate
from co_batch_master cbm where cbm.cbm_active <> 'D' and datepart(year,cbm_batch_start_dt) <> '3000'
and datepart(year,cbm_batch_start_dt) <> '4000' and datepart(year,cbm_batch_start_dt) <> '5000' and datepart(year,cbm_batch_start_dt) <> '6000'
and cbm.cmj_major_code = 'pstf'
and ((cbm_batch_start_dt between '03/03/2014' and '03/15/2014') or
(cbm_batch_end_dt between '03/03/2014' and '03/15/2014'))
order by cbm.cmn_minor_Code, cbm.cbm_batch_start_dt
Open Batch_details_Cur
fetch next from Batch_details_Cur into @BatchID, @MajorName, @Minorcode, @Intake_Capacity, @BatchStartdate, @BatchEnddate
while @@Fetch_status = 0
begin
print @Intake_Capacity
insert into #TempTable values(@MajorName,@Minorcode,@BatchStartdate,@BatchEnddate,@Intake_Capacity)
fetch next from Batch_details_Cur into @BatchID, @MajorName, @Minorcode, @Intake_Capacity, @BatchStartdate, @BatchEnddate
end
close Batch_details_Cur
Deallocate Batch_details_Cur
select * from #TempTable
end
When i exeucte the store procedure output as follows
Minorcode BatchStartdate BatchEnddate Intake_Capacity MajorName
DME 3-Mar-14 3-Mar-14 6 PSTF
GME 3-Mar-14 5-Mar-14 9 PSTF
Query as follows
select cbm.cbm_batch_id, Course_Name = (select top 1 ltrim(cmn_Minor_desc) from co_minor_master where cmn_minor_Code = cbm.cmn_minor_code), cbm.cmn_minor_Code,
Intake_Capacity = (select top 1 cbn_batch_nos from co_batch_number where cbm_batch_id=cbm.cbm_batch_id),
convert(char(12), cbm.cbm_batch_start_dt,106) as BatchStartdate, convert(char(12),cbm.cbm_batch_end_dt,106) as BatchEnddate
from co_batch_master cbm where cbm.cbm_active <> 'D' and datepart(year,cbm_batch_start_dt) <> '3000'
and datepart(year,cbm_batch_start_dt) <> '4000' and datepart(year,cbm_batch_start_dt) <> '5000' and datepart(year,cbm_batch_start_dt) <> '6000'
and cbm.cmj_major_code = 'pstf'
and ((cbm_batch_start_dt between '03/03/2014' and '03/15/2014') or
(cbm_batch_end_dt between '03/03/2014' and '03/15/2014'))
order by cbm.cmn_minor_Code, cbm.cbm_batch_start_dt
in the above query i manually give date batch startdt and batch enddt as follows
((cbm_batch_start_dt between '03/03/2014' and '03/15/2014') or
(cbm_batch_end_dt between '03/03/2014' and '03/15/2014'))
order by cbm.cmn_minor_Code, cbm.cbm_batch_start_dt
i want to set date as parameter in my above store procedure.
for that how can i do.
regards,
narasiman P.
推荐答案
使用运算符代替
CONVERT（varchar，cbm_batch_start_dt，101）> = CONVERT（varchar，StartDate，101）和CONVERT（varchar，cbm_batch_end_dt，101）< = CONVERT（varchar，EndDate，101）
Use Operators instead of between
CONVERT(varchar,cbm_batch_start_dt,101)> = CONVERT(varchar,StartDate,101) and CONVERT(varchar,cbm_batch_end_dt ,101) <= CONVERT(varchar,EndDate,101)
尝试以下方法添加参数 -
配置参数和参数数据类型 [ ^ ]
如何使用参数创建SQL Server存储过程 [ ^ ]
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159448 [ ^ ]
Try the following for adding parameters -
Configuring Parameters and Parameter Data Types[^]
How to create a SQL Server stored procedure with parameters [^]
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159448[^]
这篇关于如何在商店程序中将日期作为参数传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
