34,590
社区成员
发帖
与我相关
我的任务
分享
USE [Gethome]
GO
/****** Object: StoredProcedure [dbo].[GetMaxMinDateTime] Script Date: 05/23/2012 08:33:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 查询一个用户的日志,日志,活动,照片这四个中日期最小的一个和最小
-- Create date: 2012-05-22
-- Description: 查询一个用户的日志,日志,活动,照片这四个中日期最小的一个和最小的时间
-- =============================================
ALTER PROCEDURE [dbo].[GetMaxMinDateTime]
@ClanID int,--家族的ID
@MaxDateTime datetime OUT,--最大时间返回出去
@MinDateTime datetime OUT--最小时间返回出去
AS
BEGIN
Declare @MaxDate datetime--储存这四个中最大时间
declare @MinDate datetime--储存这四个最小时间
declare @guodu datetime--时间过度
select top 1 @MinDate=HappeTime from GH_Event where ClanID=@ClanID order by HappeTime--取得大事记的最小时间
select top 1 @guodu=B.PostTime from dbo.GH_Blog as B INNER JOIN dbo.GH_UserInfo AS U ON B.UserID=U.UserID where U.ClanID=@ClanID order by PostTime
IF @guodu<@MinDate--把日志的最小时间取出之后开始对比时间大小
BEGIN--如果过度时间没有最小时间大就把过度时间赋值给最小时间
set @MinDate=@guodu
end
select top 1 @guodu=BeginTime from GH_Activity where ClanID=@ClanID order by BeginTime
IF @guodu<@MinDate
BEGIN
set @MinDate=@guodu
end
select top 1 @guodu=P.PostTime from dbo.GH_Photo as P INNER JOIN dbo.GH_UserInfo AS U ON P.UploadUser=U.UserID WHERE U.ClanID=@ClanID order by PostTime
IF @guodu<@MinDate
BEGIN
set @MinDate=@guodu
SET @MinDateTime=@MinDate
end
select top 1 @MaxDate=HappeTime from GH_Event where ClanID=@ClanID order by HappeTime desc
select top 1 @guodu=B.PostTime from dbo.GH_Blog as B INNER JOIN dbo.GH_UserInfo AS U ON B.UserID=U.UserID where U.ClanID=@ClanID order by PostTime desc
IF @guodu>@MaxDate
BEGIN
set @MaxDate=@guodu
end
select top 1 @guodu=BeginTime from GH_Activity where ClanID=@ClanID order by BeginTime desc
IF @guodu>@MaxDate
BEGIN
set @MaxDate=@guodu
end
select top 1 @guodu=P.PostTime from dbo.GH_Photo as P INNER JOIN dbo.GH_UserInfo AS U ON P.UploadUser=U.UserID WHERE U.ClanID=@ClanID order by PostTime desc
IF @guodu>@MaxDate
BEGIN
set @MaxDate=@guodu
SET @MaxDateTime=@MaxDate
end
END
SqlParameter[] param = new SqlParameter[3];
param[0] = new SqlParameter("@ClanID", ClanID);
param[1] = new SqlParameter("@MaxDateTime", "2000-01-01");
param[1].Direction = ParameterDirection.ReturnValue;
param[2] = new SqlParameter("@MinDateTime", "2000-01-01");
param[2].Direction = ParameterDirection.ReturnValue;
DbHelper.ExecuteScalar(CommandType.StoredProcedure, "GetMaxMinDateTime", param);
string[] lostTime = new string[2];
lostTime[0] = param[1].Value.ToString();
lostTime[1] = param[2].Value.ToString();