USE [UPhoneCloud_Data]
GO
/****** Object: StoredProcedure [dbo].[U_Sp_StSelect] Script Date: 10/13/2012 14:44:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[U_Sp_StSelect]
@UserId INT,
@StoresId INT,--门店Id
@DateType INT,--日期类型1:当天2:当月3:本周4:本年5:昨天
@PageSize INT,--分页大小
@PageIndex INT,--当前页码
@Count INT OUTPUT,--
@LogLevel INT--日志等级0:服务次数1安装应用数量
As
declare @PageLowerBound int
declare @PageUpperBound int
set @PageLowerBound=@PageSize*(@PageIndex-1)
set @PageUpperBound=@PageLowerBound + @PageSize + 1
create table #tableLogs
(
IndexId int IDENTITY(1,1) NOT NULL,
StoresLogId int
)
declare @SQl nvarchar(1000)
set @SQl='select StoresLogId from U_StoresLog where 1=1'
if @UserId<>0
begin
set @SQl=@SQl + 'and UserId='+cast(@UserId as nvarchar(50))+''
end
if @StoresId <>0
begin
set @SQl=@SQl + 'and StoresId='+ CAST(@StoresId as nvarchar(50))+''
end
if @DateType<>0
begin
if @DateType=1
begin
set @SQl=@SQl + ' AND DATEDIFF(dd,LogAddtime,GETDATE())=0'
end
if @DateType=2
begin
set @SQl=@SQl + 'AND DATEDIFF(mm,LogAddtime,GETDATE())=0'
end
if @DateType=3
begin
set @SQl=@SQl + 'and datediff(wk,LogAddtime,getdate())=0'
end
if @DateType=4
begin
set @SQl=@SQl + 'and datediff(yy,LogAddtime,getdate())=0'
end
if @DateType=5
begin
set @SQl=@SQl + 'and datediff(dd,LogAddtime,getdate())=1'
end
end
if @LogLevel=0
begin
set @SQl=@SQl + 'and where LogLevel='+CAST(@LogLevel as nvarchar(50)) +''
end
else
begin
set @SQl=@SQl + 'and where LogLevel !=0'
end
set @SQl=@SQl + 'and order by LogAddtime desc'
insert into #tableLogs(StoresLogId) exec(@SQL)
SELECT U.* FROM U_StoresLog U , #tableLogs WHERE U.StoresLogId=#tableLogs.StoresLogId
AND #tableLogs.IndexId>@PageLowerBound AND #tableLogs.IndexId<@PageUpperBound
SELECT @Count=COUNT(StoresLogId) FROM #tableLogs
RETURN @Count
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货