代碼:
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
