ALTER proc [dbo].[Sp_tj_Sjtj]
(
@Communityid varchar(10),
@type varchar(5) ,
@Stime datetime, --开始时间
@Etime datetime --截止时间
)
asbegin
declare @strsql varchar(8000),
@name varchar(20),
@where varchar(50)
if @Communityid='0' or @Communityid='' or @Communityid =null or @Communityid is null
set @Communityid ='null'
IF @Stime = ''
SET @Stime = NULL
IF @Etime = ''
SET @Etime = NULL
if(@type='1')
begin
set @name='TypeName'
set @where='t.Typeid';
end
else if(@type='2')
begin
set @name='FlowCategoryName'
set @where='fc.FlowCategoryid';
end
else
begin
set @name='FlowName'
set @where='f.Flowid';
end
set @strsql ='
select isnull( '+@name+',''未知'') name ,COUNT(cid) as ''合计'','+@where+' id from tblCase c
left join tblFlow f on c.Flowid=f.Flowid
left join tblFlowCategory fc on f.FlowCategoryid =fc.FlowCategoryid
left join tblType t on fc.typeid=t.Typeid where
(CommunityID='+@Communityid+' or '+@Communityid+' is null)
and (CONVERT(varchar(100),c.HappenTime,23)>='+@Stime+' or '+@Stime+' is null)
and (CONVERT(varchar(100),c.HappenTime,23)<='+@Etime+' or '+@Etime+' is null)
group by '+@where+','+@name+'
order by ''合计'' desc'
exec(@strsql)end
我感觉就是拼字符串那有问题啊。大家帮帮忙啊。
(
@Communityid varchar(10),
@type varchar(5) ,
@Stime datetime, --开始时间
@Etime datetime --截止时间
)
asbegin
declare @strsql varchar(8000),
@name varchar(20),
@where varchar(50)
if @Communityid='0' or @Communityid='' or @Communityid =null or @Communityid is null
set @Communityid ='null'
IF @Stime = ''
SET @Stime = NULL
IF @Etime = ''
SET @Etime = NULL
if(@type='1')
begin
set @name='TypeName'
set @where='t.Typeid';
end
else if(@type='2')
begin
set @name='FlowCategoryName'
set @where='fc.FlowCategoryid';
end
else
begin
set @name='FlowName'
set @where='f.Flowid';
end
set @strsql ='
select isnull( '+@name+',''未知'') name ,COUNT(cid) as ''合计'','+@where+' id from tblCase c
left join tblFlow f on c.Flowid=f.Flowid
left join tblFlowCategory fc on f.FlowCategoryid =fc.FlowCategoryid
left join tblType t on fc.typeid=t.Typeid where
(CommunityID='+@Communityid+' or '+@Communityid+' is null)
and (CONVERT(varchar(100),c.HappenTime,23)>='+@Stime+' or '+@Stime+' is null)
and (CONVERT(varchar(100),c.HappenTime,23)<='+@Etime+' or '+@Etime+' is null)
group by '+@where+','+@name+'
order by ''合计'' desc'
exec(@strsql)end
我感觉就是拼字符串那有问题啊。大家帮帮忙啊。
(CommunityID='+@Communityid+' or '+@Communityid+' is null)
and (CONVERT(varchar(100),c.HappenTime,23)>='+@Stime+' or '+@Stime+' is null)
and (CONVERT(varchar(100),c.HappenTime,23)<='+@Etime+' or '+@Etime+' is null)
-->
IF @Stime is nul@Stime是datetime类型,不能 = ''
and (CONVERT(varchar(100),c.HappenTime,23)<='+@Etime+' or '+@Etime+' is null)
这里有问题
left join tblFlow f on c.Flowid=f.Flowid
left join tblFlowCategory fc on f.FlowCategoryid =fc.FlowCategoryid
left join tblType t on fc.typeid=t.Typeid where
(CommunityID='+@Communityid+' or '+@Communityid+' is null)
and (CONVERT(varchar(100),c.HappenTime,23)>=@Stime or @Stime is null)
and (CONVERT(varchar(100),c.HappenTime,23)<=@Etime or @Etime is null)
group by @where,@name
order by '合计' desc
不用exec
(
@Communityid varchar(10),
@type varchar(5) ,
@Stime datetime, --开始时间
@Etime datetime --截止时间
)
as
and (CONVERT(varchar(100),c.HappenTime,23)>='+@Stime+' or '+@Stime+' is null)
and (CONVERT(varchar(100),c.HappenTime,23)<='+@Etime+' or '+@Etime+' is null)
1# 既然定义了两个变量是datetime型,就不要用 convert 转换了
2# 存储过程里的判断可以去掉!
3# and c.HappenTime>='+@Stime+' or '+@Stime+' is null)
and c.HappenTime<='+@Etime+' or '+@Etime+' is null)
101 mm/dd/yy
102 yy.mm.dd
103 dd/mm/yy
104 dd.mm.yy
105 dd-mm-yy
106 dd mon yy
107 Mon dd, yy
108 hh:mm:ss
109 或者 9 mon dd yyyy hh:mi:ss:mmmAM(或者 PM)
110 mm-dd-yy
111 yy/mm/dd
112 yymmdd
113 或者 13 dd mon yyyy hh:mm:ss:mmm(24h)
114 hh:mi:ss:mmm(24h)
120 或者 20 yyyy-mm-dd hh:mi:ss(24h)
121 或者 21 yyyy-mm-dd hh:mi:ss.mmm(24h)
126 yyyy-mm-ddThh:mm:ss.mmm(没有空格)
130 dd mon yyyy hh:mi:ss:mmmAM
131
and (CONVERT(varchar(100),c.HappenTime,23)<='''+@Etime+''' or '''+@Etime+''' is null) ??????
#2,就你写的这个set @Etime=null 就算转换为了字符串再拼接起来,你的@strsql也是null你可以print出来看看
set @strsql ='
select isnull( '+@name+',''未知'') name ,COUNT(cid) as ''合计'','+@where+' id from tblCase c
left join tblFlow f on c.Flowid=f.Flowid
left join tblFlowCategory fc on f.FlowCategoryid =fc.FlowCategoryid
left join tblType t on fc.typeid=t.Typeid where
(CommunityID='+@Communityid+' or '+@Communityid+' is null)
and (CONVERT(varchar(100),c.HappenTime,23)>='''+convert(varchar(100),@stime,23)+''' or '''+convert(varchar(100),@stime,23)+''' is null)
and (CONVERT(varchar(100),c.HappenTime,23)<='''+convert(varchar(100),@Etime,23)+''' or '''+convert(varchar(100),@Etime,23)+''' is null)
group by '+@where+','+@name+'
order by ''合计'' desc'
ALTER proc [dbo].[Sp_tj_Sjtj]
(
@Communityid varchar(10),
@type varchar(5) ,
@Stime varchar(19), --开始时间
@Etime varchar(19) --截止时间
)
as
大牛们都在等呢!
这样就可以查出来。
select * from tblCase c
left join tblFlow f on c.Flowid=f.Flowid
left join tblFlowCategory fc on f.FlowCategoryid =fc.FlowCategoryid
left join tblType t on fc.typeid=t.Typeid where
(CommunityID=2 or Communityid is null)
and (CONVERT(varchar(100),c.HappenTime,23)>='2011-03-01' or '+@Stime+' is null)
and (CONVERT(varchar(100),c.HappenTime,23)<='2011-03-05' or '+@Etime+' is null)
set @strsql ='
select isnull( '+@name+',''未知'') name ,COUNT(cid) as ''合计'','+@where+' id from tblCase c
left join tblFlow f on c.Flowid=f.Flowid
left join tblFlowCategory fc on f.FlowCategoryid =fc.FlowCategoryid
left join tblType t on fc.typeid=t.Typeid where
(CommunityID='+@Communityid+' or '+@Communityid+' is null)
and c.HappenTime>='''+convert(varchar(10),@stime,120)+''' or '''+convert(varchar(10),@stime,120)+''' is null)
and c.HappenTime<='''+convert(varchar(10),@Etime,120)+''' or '''+convert(varchar(10),@Etime,120)+''' is null)
group by '+@where+','+@name+'
order by ''合计'' desc'