如何在商店程序中将日期作为参数传递

本文介绍了如何在商店程序中将日期作为参数传递的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 29岁程序员，3月因学历无情被辞！ 我的商店程序如下 设置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 查询如下 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 ONset QUOTED_IDENTIFIER ONGOALTER proc [dbo].[Presea_Intakerpts]asbeginset nocount ondeclare @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 BatchEnddatefrom 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_dtOpen Batch_details_Curfetch next from Batch_details_Cur into @BatchID, @MajorName, @Minorcode, @Intake_Capacity, @BatchStartdate, @BatchEnddatewhile @@Fetch_status = 0beginprint @Intake_Capacityinsert into #TempTable values(@MajorName,@Minorcode,@BatchStartdate,@BatchEnddate,@Intake_Capacity)fetch next from Batch_details_Cur into @BatchID, @MajorName, @Minorcode, @Intake_Capacity, @BatchStartdate, @BatchEnddateendclose Batch_details_CurDeallocate Batch_details_Curselect * from #TempTableend 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 PSTFQuery 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 BatchEnddatefrom 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_dti 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 betweenCONVERT(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[^] 这篇关于如何在商店程序中将日期作为参数传递的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！