Question

sql中如何设置存储过程的默认值为当前日期

create proc proc_borrow
@willdate datetime=dateadd(yy,-1,GETDATE()),@retumdate datetime=GETDATE()
as
select * from Borrow where WillDate between @willdate and @retumdate
go

Answer 1

• 存储过程参数的默认值只允许常量和null. 可以做如下修改：
  create proc proc_borrow
  @willdate datetime=null,@retumdate datetime=null
  as
  select * from Borrow where WillDate between isnull(@willdate,dateadd(yy,-1,GETDATE())) and isnull(@retumdate,GETDATE())
  go
  

Answer 2

create proc proc_borrow
as
begin
declare @willdate datetime
declare @returndate datetime
SET @willdate = dateadd(yy,-1,GETDATE())
SET @returndate=GETDATE()

select * from Borrow where WillDate between @willdate and @returndate
end
go

Answer 3

存储过程参数的默认值只允许常量和null. 可以做如下修改：
create proc proc_borrow
@willdate datetime=null,@retumdate datetime=null
as
select * from Borrow where WillDate between isnull(@willdate,dateadd(yy,-1,GETDATE())) and isnull(@retumdate,GETDATE())
go