诸位好:我想写一个简单的存储过程,需要传入两个带默认值的日期参数,其中第二个参数默认为今天,如下所示:
CREATE PROCEDURE getRecords
@dtmBdate datetime = '1990-01-01',
@dtmEdate datetime = getdate()
AS
select * from TabLC where dealdate between @dtmBdate and @dtmEdate
可是在执行此语句时,系统提示有语法错误,
后来我试着将第二个参数的赋值格式改成:@dtmEdate datetime = convert(varchar(10),getdate(),120),还是出现相同的错误提示,请问各位此语句应该怎么写?谢谢!
CREATE PROCEDURE getRecords
@dtmBdate datetime = '1990-01-01',
@dtmEdate datetime = getdate()
AS
select * from TabLC where dealdate between @dtmBdate and @dtmEdate
可是在执行此语句时,系统提示有语法错误,
后来我试着将第二个参数的赋值格式改成:@dtmEdate datetime = convert(varchar(10),getdate(),120),还是出现相同的错误提示,请问各位此语句应该怎么写?谢谢!
@dtmBdate datetime = '1990-01-01',
--@dtmEdate datetime = getdate()不要代入,都是得到GetDate
AS
Declare @dtmEdate DateTime
Set @dtmEdate = GetDate()select * from TabLC where dealdate between @dtmBdate and @dtmEdateGo
@dtmBdate datetime,
@dtmEdate datetime
AS
set @dtmBdate = '1990-01-01'
set @dtmEdate = = getdate()
select * from TabLC where dealdate between @dtmBdate and @dtmEdate
@dtmBdate datetime = '1990-01-01',
@dtmEdate datetime = getdate
AS
select * from TabLC where dealdate between @dtmBdate and @dtmEdate
GO语法检测通过
谢谢!你这个方法是可行的!能告诉我如果按我的格式写应该怎么写吗?
谢谢你的支持!
但是如果这样的话无论你调用此过程时传入的什么参数结果都是一样的吧?
谢谢!按你说的我试了,的确能编译通过!
可是调用时该如何传参呢?
我是这样写的:exec getRecords '2005-01-01',default,系统老是提示:“从字符串转换为 datetime 时发生语法错误。”,请分析一下原因。
个人认为要默认为当前时间好像有点困难有个替代的方法是,不给@dtmEdate 默认值
而是在执行过程中判断,调用时是否未设定@dtmEdate参数,未设定时取当前时间CREATE PROCEDURE getRecords
@dtmBdate datetime = '1990-01-01',
@dtmEdate datetime
AS
IF (@dtmEdate = ' ')
BEGIN
SET @dtmEdate = getdate()
END
select * from TabLC where dealdate between @dtmBdate and @dtmEdateGO调用
exec getRecords '2005-01-01',''
@dtmBdate datetime = null,
@dtmEdate datetime = null
AS
set @dtmEdate = getdate()
if(@dtmBdate=null)
begin
set @dtmBdate = CAST('1990-01-01' AS datetime)
end
select * from TabLC where dealdate between @dtmBdate and @dtmEdate
GO
CREATE PROCEDURE getRecords
@dtmBdate datetime,
@dtmEdate datetime
AS
if(@dtmBdate=null)
begin
set @dtmBdate = CAST('1990-01-01' AS datetime)
end
select * from TabLC where dealdate between @dtmBdate and @dtmEdate
GO
你好,请明示,谢谢!
谢谢诸位,诸位所提的思路都是可行的,这么说要想按我的问题中的那种形式是不行的了?
@dtmBdate datetime,
@dtmEdate datetime
AS
set @dtmBdate = '1990-01-01'
set @dtmEdate = = getdate()
select * from TabLC where dealdate between @dtmBdate and @dtmEdate好像是在定义时调用系统函数不行吧
在参数定义时,不能调用系统函数来做默认值所以,要默认取当前时间的话,
可能只有在存储过程里做个判断,如果调用者未给出参数,取getdate()至少 waterblue1981(零风)和du9232(木土四正) 提供的方法不可行
而其他大部分帖子是让你把
@dtmBdate 和@dtmEdate 在存储过程里写死,这样你都不用传参数了,当然也不行当然了,我也不是高手,有其他更好的解决方案也说不定
谢谢老兄的支持!
我也倾向于你的看法:“在参数定义时,不能调用系统函数来做默认值”。而且好象也不能调用系统函数来做传入参数,可能系统函数就不能作为存储过程的参数来处理。以上仅是个人看法,如有错误,还请大家指教!