请问这样的存储过程怎么写,有没有更简单的方法?
create proc GetPlanActualDate
(
@startDate datetime,
@asofDate datetime
)
asif(@startDate和@asofDate都为空)
begin
select CONVERT( nvarchar(10),min(UserDate),120 )+' -- '+ convert(nvarchar(10),max(UserDate),120) as UserDate from tableAendif(@startDate不为空)beginCONVERT( nvarchar(10),@startDate,120 )+' -- '+ convert(nvarchar(10),max(UserDate),120) as UserDate from tableAendif(@asofDate不为空)begin CONVERT( nvarchar(10), min(UserDate),120 )+' -- '+ convert(nvarchar(10),@asofDate,120) as UserDate from tableAendif(@startDate和@asofDate不为空)begin
CONVERT( nvarchar(10),@startDate),120 )+' -- '+ convert(nvarchar(10),@asofDate,120) as UserDate from tableA
end
create proc GetPlanActualDate
(
@startDate datetime,
@asofDate datetime
)
asif(@startDate和@asofDate都为空)
begin
select CONVERT( nvarchar(10),min(UserDate),120 )+' -- '+ convert(nvarchar(10),max(UserDate),120) as UserDate from tableAendif(@startDate不为空)beginCONVERT( nvarchar(10),@startDate,120 )+' -- '+ convert(nvarchar(10),max(UserDate),120) as UserDate from tableAendif(@asofDate不为空)begin CONVERT( nvarchar(10), min(UserDate),120 )+' -- '+ convert(nvarchar(10),@asofDate,120) as UserDate from tableAendif(@startDate和@asofDate不为空)begin
CONVERT( nvarchar(10),@startDate),120 )+' -- '+ convert(nvarchar(10),@asofDate,120) as UserDate from tableA
end
create proc GetPlanActualDate
(
@startDate datetime,
@asofDate datetime
)
as
DECLARE @start datetime
DECLARE @end datetime
IF (@startDate为空)
@start=CONVERT( nvarchar(10),min(UserDate),120 )
ELSE
@start=CONVERT( nvarchar(10),@startDate),120 )
----------------
IF (@asofDate不为空)
@end=CONVERT( nvarchar(10),max(UserDate),120 )
ELSE
@end=CONVERT( nvarchar(10),@asofDate),120 )select @start+' -- '+@end as UserDate from tableA
(
@startDate datetime,
@asofDate datetime
)
as select CONVERT( nvarchar(10),isnull(@startDate, min(UserDate)),120 )+' -- '+ convert(nvarchar(10),isnull(@asofDate, max(UserDate)),120) as UserDate from tableA
(
@startDate datetime,
@asofDate datetime
)
as
begin
if @startDate='' and @asofDate=''
begin
select CONVERT( nvarchar(10),min(UserDate),120 )+' -- '+ convert(nvarchar(10),max(UserDate),120) as UserDate from tableA
end else if @startDate is not null or @asofDate is not null
begin
select CONVERT( nvarchar(10),@startDate,120 )+' -- '+ convert(nvarchar(10),max(UserDate),120) as UserDate from tableA
select CONVERT( nvarchar(10), min(UserDate),120 )+' -- '+ convert(nvarchar(10),@asofDate,120) as UserDate from tableA
end else
begin
select CONVERT(nvarchar(10),@startDate),120)+' -- '+ convert(nvarchar(10),@asofDate,120) as UserDate from tableA
end
end
if isnull(@startDate,'')='' and isnull(@asofDate,'')=''
begin
print 1
end
else if isnull(@startDate,'')='' and isnull(@asofDate,'')<>''
begin
print 2
end
else if isnull(@startDate,'')<>'' and isnull(@asofDate,'')=''
begin
print 3
end
else
begin
print 4
end
(
@startDate datetime,
@asofDate datetime
)
as select top 1 CONVERT( nvarchar(10),isnull(@startDate, min(UserDate)),120 )+' -- '+ convert(nvarchar(10),isnull(@asofDate, max(UserDate)),120) as UserDate from tableA
(
@startDate datetime,
@asofDate datetime
)
as
begin
select CONVERT( nvarchar(10),(case when @startDate is not null then @startDate else min(UserDate) end),120 )+
' -- '+ convert(nvarchar(10),(case when @asofDate is not null then @asofDate else max(UserDate) end),120) as UserDate from tableA
end