Sql Server实现按时间统计存储过程(涉及临时表,动态Sql语句,Cast用法,Sp_Execute 多参数调用,游标技术等)

代码:

ALTER                   Procedure UserSiteNowDataHourTotalView_List(
@StartTime varchar(25),
@EndTime varchar(25),
@SiteID int
)
AS

declare @SQLString nvarchar(200)
declare @SQLTemp nvarchar(2000)
declare @TotalNum nvarchar(25)
set @StartTime=replace(@StartTime,'-','.')
set @EndTime=replace(@EndTime,'-','.')
--set @SQLString=N'SELECT case when [TotalNum]=null then 0 else [TotalNum] End from UserSiteNowDataHourTotalView WHERE (VisitTime between '''+@StartTime
--定义全局游标 declare   tb   cursor   global for

set @SQLString=N'SELECT @TotalNum=sum([TotalNum]) from UserSiteNowDataHourTotalView WHERE (VisitTime between '''+@StartTime
set @SQLString=@SQLString+N''' and '''+@EndTime+''')'

--if @SiteID!=0
--set @SQLString=@SQLString+N' and SiteID='+cast(@SiteID as nvarchar)
--建立临时表
Create Table #Hour_Temp(
ID int IDENTITY(1,1) NOT NULL,
[HourNum] int,
[TotalNum] int,
primary key(ID)
)

declare @i int

set @i=0
while @i<24
begin
--set @SQLTemp=N'declare   tb   cursor for '+@SQLString
set @SQLTemp=@SQLString
set @SQLTemp=@SQLTemp+N' and [Hour]='+cast(@i as nvarchar)
set @TotalNum = null
execute SP_Executesql @SQLTemp,N'@TotalNum   INT   output',@TotalNum   OUTPUT
if @TotalNum is null
set @TotalNum=0

print @TotalNum

/*
@S,N'@RET   INT   output',@RET   OUTPUT
open   tb  
  fetch   tb    
  close   tb  
  deallocate   tb
*/
insert into #Hour_Temp(HourNum,TotalNum) values (@i,@TotalNum)

--insert into #Hour_Temp(HourNum,TotalNum) values(@i,@i)
--set @SQLTemp=@SQLTemp+N' union all '+@SQLString+N' and DatePart(hh,VisitTime)='+cast(@i as nvarchar)
set @i=@i+1
end

--exec sp_executesql @SQLTemp
select * from #Hour_Temp order by HourNum asc

Sp_ExecuteSql多参数调用演示

Create Procedure TestExecuteSql
AS

declare @Hour int
declare @tablename varchar(200)
declare @sql nvarchar(200)
declare @value int

set @tablename='UserSiteNowDataHourTotalView'
set @Hour=15
--set @sql=N'select @value=TotalNum from '+@tablename+' where [Hour]='+cast(@Hour as nvarchar)
set @sql=N'select @value=TotalNum from '+@tablename+' where [Hour]=@Hour'
--set @sql=@sql
execute sp_Executesql @sql,N'@value int output,@Hour int',@value output,@Hour=12

print @value