sqlserver调用存储过程问题
ALTER proc [dbo].[proc_searchByDate](@starttime datetime,@endtime datetime,@webid char(17),@sort varchar(50),@userid char(17))
as
declare @sqlstr varchar(4000)
declare @sqlwhere varchar(4000)
declare @sql varchar(4000)
if @webid <> null and @webid <>'' and @webid <>'-1'
begin
set @sql=' webid,'
set @sqlwhere=' where webid='''+@webid+''''
end
set @sqlstr='
select'+@sql+' CONVERT(varchar(10) ,enterdate,120) enterdate,COUNT(*) c1,SUM(dialoguenum) d1 into ##t1
from tb_chat_Visitors where userid='''+@userid+'''
enterdate between '''+CONVERT(varchar(10), @starttime,120)+''' and '''+CONVERT(varchar(10), @endtime,120)+'''
group by '+@sql+'CONVERT(varchar(10) ,enterdate,120)'
exec(@sqlstr)
set @sqlstr='
select'+@sql+' CONVERT(varchar(10) ,enterdate,120) enterdate,COUNT(*) c2,SUM(dialoguenum) d2 into ##t2
from tb_chat_Visitors where userid='''+@userid+''' and isvalid=2
enterdate between '''+CONVERT(varchar(10), @starttime,120)+''' and '''+CONVERT(varchar(10), @endtime,120)+'''
group by '+@sql+'CONVERT(varchar(10) ,enterdate,120)'
exec(@sqlstr)
set @sqlstr='
select'+@sql+' CONVERT(varchar(10) ,enterdate,120) enterdate,COUNT(*) c3,SUM(dialoguenum) d3 into ##t3
from tb_chat_Visitors where userid='''+@userid+''' and dialoguevalid=1
enterdate between '''+CONVERT(varchar(10), @starttime,120)+''' and '''+CONVERT(varchar(10), @endtime,120)+'''
group by '+@sql+'CONVERT(varchar(10) ,enterdate,120)'
exec(@sqlstr)
if @sql<>null and @sql<>''
begin
set @sql=' ##t1.webid,'
end
set @sqlstr='
select '+@sql+'CONVERT(varchar(10) ,##t1.enterdate,120) enterdate,isnull(avg(##t1.c1),0) 点击量,isnull(avg(##t1.d1),0) 聊天量,isnull(avg(##t2.c2),0) 有效名片量,isnull(avg(##t3.c3),0) 有效聊天量,isnull(avg(##t1.d1),0)/isnull(avg(##t1.c1),0)*100 聊天转换率,isnull(avg(##t2.c2),0)/isnull(avg(##t1.d1),0)*100 有效名片转换率 from ##t1
left join ##t2 on ##t2.enterdate=##t1.enterdate
left join ##t3 on ##t3.enterdate=##t1.enterdate '+@sqlwhere+'
group by '+@sql+'CONVERT(varchar(10) ,##t1.enterdate,120) '+@sort
exec(@sqlstr)
调用之后没有显示结果 显示的是命令已成功完成
存储 sqlserver sql
[解决办法]
先试试这个，如果空白，通常情况下是where中出现问题，最多情况下是日期匹配上的问题
ALTER proc [dbo].[proc_searchByDate](@starttime datetime,@endtime datetime,@webid char(17),@sort varchar(50),@userid char(17))
as
declare @sqlstr varchar(4000)
declare @sqlwhere varchar(4000)
declare @sql varchar(4000)
if @webid <> null and @webid <>'' and @webid <>'-1'
begin
set @sql=' webid,'
set @sqlwhere=' where webid='''+@webid+''''
end
set @sqlstr='
select'+@sql+' CONVERT(varchar(10) ,enterdate,120) enterdate,COUNT(*) c1,SUM(dialoguenum) d1 into ##t1
from tb_chat_Visitors where userid='''+@userid+'''
enterdate between '''+CONVERT(varchar(10), @starttime,120)+''' and '''+CONVERT(varchar(10), @endtime,120)+'''
group by '+@sql+'CONVERT(varchar(10) ,enterdate,120)'
PRINT(@sqlstr)
