22,210
社区成员
发帖
与我相关
我的任务
分享存储过程如何不写参数查询所有记录
例如：我写了一个存储过程有两参数如何不输入开始日期和结束日期就就查出所有记录
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 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
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 b.fdate between @beginDate and @endDate
group by c.fname
order by 换货金额 desc
select * from #temp
drop table #temp
GO
...全文
请发表友善的回复…
发表回复
jing_an_2010 2009-08-14
- 打赏
- 举报
学习了。。
stay8790 2009-04-22
- 打赏
- 举报
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
(@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
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
KevyLi 2009-04-18
- 打赏
- 举报
SET QUOTED_IDENTIFIER ON
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
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
abcdefgdeng 2009-04-18
- 打赏
- 举报
设置默认值了，开始日期为'1900-01-01',结束日期为'3000-01-01'
@beginDate datetime='1900-01-01',
@endDate datetime='3000-01-01'
@beginDate datetime='1900-01-01',
@endDate datetime='3000-01-01'
ision 2009-04-18
- 打赏
- 举报
你的问题我也遇到过。楼上有人说过取默认值，我也采用了这种方法，
between (isnull(@beginDate,'1900-01-01')='1900-01-01' or a.beginDate=@beginDate) and (isnull(@endDate,getdate())=getdate() or a.endDate=@endDate)
between (isnull(@beginDate,'1900-01-01')='1900-01-01' or a.beginDate=@beginDate) and (isnull(@endDate,getdate())=getdate() or a.endDate=@endDate)
wsd2616412 2009-04-18
- 打赏
- 举报
可以考虑用隐含参数
randy612400 2009-04-17
- 打赏
- 举报
学习
Myth_NiuNiu 2009-04-17
- 打赏
- 举报
顶一顶，学习
xp1056 2009-04-17
- 打赏
- 举报
或者使用动态SQL,如果传的参数为null（看你自己设定的不传参数是怎么定义的），写动态SQL排除该条件就可以了
jianjian51 2009-04-17
- 打赏
- 举报
SET QUOTED_IDENTIFIER ON
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
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
michael_chaw 2009-04-17
- 打赏
- 举报
--设置默认值
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
rofuto 2009-04-17
- 打赏
- 举报
学习
h465716405 2009-04-17
- 打赏
- 举报
学习一下
qtlhn 2009-04-17
- 打赏
- 举报
好复杂
長胸為富 2009-04-16
- 打赏
- 举报
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 @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任意一个为空或全为空，都会返回所有记录，而且没有具体年份的限制。
@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任意一个为空或全为空，都会返回所有记录，而且没有具体年份的限制。
Roc_Lee 2009-04-16
- 打赏
- 举报
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
设置默认值
Radar2006 2009-04-15
- 打赏
- 举报
设定参数默认值
ntahua 2009-04-15
- 打赏
- 举报
(b.fdate between @beginDate and @endDate or (@begindate is null and @endDate is null))
skyctr 2009-04-15
- 打赏
- 举报
同意11楼的
wlkjhxd 2009-04-15
- 打赏
- 举报
判断一下日期是不是为NULL，如果是，那么就把日期的谓词拿掉就行了，用得着这么麻烦吗，拿掉之后的效率还更高一些
随便说一下，这个有必要用到临时表吗，有点画蛇添足
随便说一下，这个有必要用到临时表吗，有点画蛇添足
加载更多回复（53）
# 创建海量数据实验环境 创建完成海量数据的存储过程，产生一千条记录和一百万或二百万条记录，操作上面没有任何区别，唯一区别是占用的时间不同，具备海量数据后，可以为系统压力测试实验，做好物理准备。...
