例如:我写了一个存储过程有两参数如何不输入开始日期和结束日期就就查出所有记录
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER proc proc_bosChangeItem(
@beginDate datetime,
@endDate datetime
)
as
set nocount on
select IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量 into #temp from t_boschangeitementry a
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0 and b.fdate between @beginDate and @endDate
group by c.fname
order by 换货金额 desc
select * from #temp
drop table #temp
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GOALTER proc proc_bosChangeItem(
@beginDate datetime,
@endDate datetime
)
as
set nocount on
select IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量 into #temp from t_boschangeitementry a
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0 and b.fdate between @beginDate and @endDate
group by c.fname
order by 换货金额 desc
select * from #temp
drop table #temp
GO
where b.fchecker>0 and b.fdate between isnull(@beginDate,0) and isnull(@endDate ,'2050-1-1')
GO
SET ANSI_NULLS ON
GO ALTER proc proc_bosChangeItem(
@beginDate datetime =NULL,
@endDate datetime =NULL)
as
set nocount on
select IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量 into #temp from t_boschangeitementry a
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0 and b.fdate between isnull(@beginDate,0) and isnull(@endDate,'9999-12-31')
group by c.fname
order by 换货金额 desc
select * from #temp
drop table #temp
GO
--调用
exec proc_bosChangeItem
GO
SET ANSI_NULLS ON
GO ALTER proc proc_bosChangeItem(
@beginDate datetime ='1900-01-01',
@endDate datetime ='9999-12-31')
as
set nocount on
select IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量 into #temp from t_boschangeitementry a
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0 and b.fdate between @beginDate and @endDate
group by c.fname
order by 换货金额 desc
select * from #temp
drop table #temp
GO 要不这样吧,直截了当。
@beginDate datetime ='1900-01-01',
@endDate datetime ='9999-12-31')
as
set nocount on
select * from sysobjects where crdate between @beginDate and @endDate
GO exec proc_bosChangeItem -->测试成功
@beginDate datetime,
@endDate datetime
)
as
set nocount on select IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量
into #temp
from t_boschangeitementry a left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0
and b.fdate between @beginDate and @endDate
or b.fchecker>0
and @BeginDate = ''
and @endDate = ''
group by c.fname
order by 换货金额 desc select * from #temp drop table #temp GO
这样不就行了吗!!!
@beginDate datetime=null,
@endDate datetime=null )
as
set nocount on
select IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量 into #temp from t_boschangeitementry a
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0 and b.fdate between isnull(@beginDate,'1753-1-1') and isnull(@endDate,'9999-12-31 23:59:59')
group by c.fname
order by 换货金额 desc
select * from #temp
drop table #temp
GO
if(isnull(@beginDate,'') ='')select IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量 into #temp from t_boschangeitementry a
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0 group by c.fname
order by 换货金额 desc
select * from #temp
drop table #temp elseselect IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量 into #temp from t_boschangeitementry a
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0 and b.fdate between isnull(@beginDate,'1753-1-1') and isnull(@endDate,'9999-12-31 23:59:59')
group by c.fname
order by 换货金额 desc
select * from #temp
drop table #temp
还有就是codeb.fdate between isnull(@beginDate,b.fdate) and isnull(@endDate,b.fdate) 这种很经典的方法可以使用一下
create proc proc_bosChangeItem
(
@beginDate datetime = null,
@endDate datetime =null
)
as
set nocount on
declare @MinDate as datetime
declare @MaxDate as datetime
select @MinDate=min(crdate) from sysobjects---表中出现的最小日期
select @MaxDate=max(crdate) from sysobjects---表中出现的最大日期
select *
from sysobjects
where
crdate between isnull(@beginDate,@MinDate) and isnull(@endDate,@MaxDate)
GO exec proc_bosChangeItem ----不限定日期,查询表中所有的数据
exec proc_bosChangeItem default,'2008-08-08'---限定截至日期
exec proc_bosChangeItem '2008-08-08',default----限定开始日期
----------
楼主的可以根据自己的要求,来参考下。
你看明白了就知道怎么做了
没细看你的东西,所以没有直接写你要的答案ALTER PROC [dbo].[WMStkinMtManage]
@WMStkinNo VARCHAR(20),
@WMStkinType varchar(10),
@WMStkinStatus VARCHAR(10),
@BeginDate VARCHAR(20),
@EndDate VARCHAR(20)
AS
BEGIN
SELECT a.WMStkinNo, b.SectionValue AS WMStkinTypeText ,c.SectionValue AS WMStkinStatusText,
a.RelatNo, a.SupplierNo,a.Fee, a.Memo,
a.CreateTime, a.CreatePerNo, a.CreatePerName,
a.UpdateTime, a.UpdatePerNo, a.UpdatePerName,
a.AuditTime, a.AuditPerNo, a.AuditPerName,
a.CancelTime, a.CancelPerNo, a.CancelPerName
FROM WMStkinMt a JOIN dbo.MDRefDataDt b ON a.WMStkinType = b.SectionNo
AND b.ObjectNo = 'WMStkinType'
JOIN MDRefDataDt c on a.WMStkinStatus = c.SectionNo
AND c.ObjectNo = 'WMStkinStatus'
WHERE CreateTime BETWEEN @BeginDate AND @EndDate
AND (CASE WHEN ISNULL(@WMStkinNo, '') = '' THEN 1
WHEN a.WMStkinNo LIKE '%'+ @WMStkinNo +'%' THEN 1
END ) = 1
AND (CASE WHEN ISNULL(@WMStkinType, '') = '' THEN 1
WHEN a.WMStkinType = @WMStkinType THEN 1
END ) = 1
AND (CASE WHEN ISNULL(@WMStkinStatus, '') = '' THEN 1
WHEN a.WMStkinStatus = @WMStkinStatus THEN 1
END ) = 1
END
@beginDate datetime,
@endDate datetime )
as
set nocount on
if @beginDate is not null and @endDate is not null
select IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量 into #temp from t_boschangeitementry a
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0 and b.fdate between @beginDate and @endDate
group by c.fname
order by 换货金额 desc
else if @beginDate is not null
select IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量 into #temp from t_boschangeitementry a
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0 and b.fdate>=@beginDate
group by c.fname
order by 换货金额 desc
else if @endDate is not null
select IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量 into #temp from t_boschangeitementry a
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0 and b.fdate<=@endDate
group by c.fname
order by 换货金额 desc
else
select IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量 into #temp from t_boschangeitementry a
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0
group by c.fname
order by 换货金额 desc
select * from #temp
drop table #temp
GO
还得给写一个???例如:我写了一个存储过程有两参数如何不输入开始日期和结束日期就就查出所有记录
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO ALTER proc proc_bosChangeItem(
@beginDate datetime,
@endDate datetime )
as
set nocount on
select IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量 into #temp from t_boschangeitementry a
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0 AND (CASE WHEN @beginDate ISNULL THEN 1
WHEN b.fdate >= @beginDate THEN 1 END) = 1
AND (CASE WHEN @beginDate ISNULL THEN 1
WHEN b.fdate >= @ endDate THEN 1 END) = 1group by c.fname
order by 换货金额 desc
select * from #temp
drop table #temp
GO 这样能明白吗?
随便说一下,这个有必要用到临时表吗,有点画蛇添足
ALTER proc proc_bosChangeItem(
@beginDate datetime='1900-01-01',
@endDate datetime =getdate())
as
set nocount on
select IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量 into #temp from t_boschangeitementry a
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0 and b.fdate between @beginDate and @endDate
group by c.fname
order by 换货金额 desc
select * from #temp
drop table #temp
GO
设置默认值
@beginDate datetime = null,
@endDate datetime = null
)
as
set nocount on
select IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量 into #temp from t_boschangeitementry a
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0 and (b.fdate between @beginDate and @endDate OR 0 = isnull(@beginDate,0) OR 0 = isnull(@endDate,0))
group by c.fname
order by 换货金额 desc
select * from #temp
drop table #temp
GO
PS:(b.fdate between @beginDate and @endDate OR 0 = isnull(@beginDate,0) OR 0 = isnull(@endDate,0))
这个只要@beginDate和@endDate任意一个为空或全为空,都会返回所有记录,而且没有具体年份的限制。
--设置默认值ALTER proc proc_bosChangeItem(
@beginDate datetime=0,
@endDate datetime='9999-12-31')
as
set nocount on
select IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量 into #temp from t_boschangeitementry a
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0 and b.fdate between @beginDate and @endDate
group by c.fname
order by 换货金额 desc
select * from #temp
drop table #temp
GO
SET ANSI_NULLS ON
GO ALTER proc proc_bosChangeItem(
@beginDate datetime='1900-01-01',
@endDate datetime='2999-01-01' )
as
set nocount on
select IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量 into #temp from t_boschangeitementry a
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0 and b.fdate between @beginDate and @endDate
group by c.fname
order by 换货金额 desc
select * from #temp
drop table #temp
GO
@endDate datetime='3000-01-01'
GO
SET ANSI_NULLS ON
GO ALTER proc proc_bosChangeItem(
@beginDate datetime,
@endDate datetime )
as
set nocount on
set @beginDate = isnull(@beginDate, CONVERT(DATETIME, '1753-01-01', 121))
set @endDate = isnull(@endDate, CONVERT(DATETIME, '9999-12-31 23:59:59.998', 121))
select IDENTITY(int,1,1) AS 名次, c.fname as 部门,sum(a.famount) as 换货金额 ,sum(a.fqty) 换货数量 into #temp from t_boschangeitementry a
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0 and b.fdate between @beginDate and @endDate
group by c.fname
order by 换货金额 desc
select * from #temp
drop table #temp
GO
between (isnull(@beginDate,'1900-01-01')='1900-01-01' or a.beginDate=@beginDate) and (isnull(@endDate,getdate())=getdate() or a.endDate=@endDate)
left join t_boschangeitem b on a.fid=b.fid
left join t_department c on b.fdeptid=c.fitemid
where b.fchecker>0 and
(@beginDate is null or b.fdate >=@beginDate )
and ( @endDate is null or b.fdate <= @endDate )
group by c.fname
order by 换货金额 desc
select * from #temp
drop table #temp
GO