SQL数据存储过程
来源:互联网 发布:澳洲人工智能专业 编辑:程序博客网 时间:2024/05/29 08:26
USE [sina]
GO
/****** Object: StoredProcedure [dbo].[sortGroup] Script Date: 03/30/2012 18:43:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[sortGroup]
@groupid int,
@up bit,
@createuser int
as
declare @dqsort int
declare @shyi int
declare @syid int
declare @xyid int
select @dqsort=Sort from T_Group where Group_Id=@groupid andcreate_user=@createuser --取出其序号
if @up = 1
begin
select top 1 @syid=Group_Id,@shyi=Sort from T_Group where Sort<@dqsort and create_user=@createuser order by Sort desc --取出其上一条Id来
end
else
begin
select top 1 @syid=Group_Id,@shyi=Sort from T_Group where Sort > @dqsort and create_user=@createuser order by Sort asc
end
begin tran
update T_Group set sort=@dqsort where Group_Id=@syid --把上一条的sort高为当前的条的Sort
update T_Group set Sort=@shyi where Group_id = @groupid
if @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
GO
/****** Object: StoredProcedure [dbo].[sendmsg] Script Date: 03/30/2012 18:43:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[sendmsg]
@pageindex int,
@pagenum int,
@userid int
as
select * from (select ROW_NUMBER() over(order by commentDate desc) as ss, * from T_Comment tc join T_user tu
on tc.Comment_Useid=tu.userid where Comment_Useid=@userid) t
where t.ss>(@pageindex-1)*@pagenum and t.ss<=@pageindex*@pagenum
GO
/****** Object: StoredProcedure [dbo].[PageSinaGroup] Script Date: 03/30/2012 18:43:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[PageSinaGroup]
@pageindex int,
@pagenum int,
@userid int,
@groupid int
as
select t.Use_Pic,t.NickName,t.Memo,t.SayContent,t.SayFrom,t.SayDatetime,t.userid,t.SayId,t.Discuss,t.Transmit,
(case when DATEDIFF(SECOND,SayDatetime,GETDATE())<60 then STR(DATEDIFF(SECOND,SayDatetime,GETDATE()))+'秒前'
when DATEDIFF(MINUTE,SayDatetime,GETDATE())<60 then str(DATEDIFF(MINUTE,SayDatetime,GETDATE())) +'分钟前'
when DATEDIFF(HOUR,SayDatetime,GETDATE())<24 then str(DATEDIFF(HOUR,SayDatetime,GETDATE()))+'个小时前'
when DATEDIFF(DAY,SayDatetime,GETDATE())<31 then str(DATEDIFF(DAY,SayDatetime,GETDATE()))+'天前'
when DATEDIFF(MONTH,SayDatetime,GETDATE())<12 then str(DATEDIFF(MONTH,SayDatetime,GETDATE()))+'个月前'
else STR(DATEDIFF(YEAR,SayDatetime,GETDATE())) +'年前'
end) datetime1
from (select ROW_NUMBER() over(order by SayDatetime desc) as ss,ts.SayId,tu.userid,tu.Use_Pic,tu.NickName,tf.Memo,
ts.SayContent,ts.SayDatetime,ts.SayFrom,ts.Discuss,ts.TFPublic,ts.Transmit
from T_FriendList tf join T_User tu on tf.Friend_id=tu.userid join T_Say ts on tf.Friend_id=ts.[User_id]
where tf.[User_id]=@userid and tf.GroupId=@groupid) t
where t.ss>(@pageindex-1)*@pagenum and t.ss<=@pageindex*@pagenum order by SayDatetime desc
GO
/****** Object: StoredProcedure [dbo].[PageSinaAll] Script Date: 03/30/2012 18:43:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[PageSinaAll]
@pageindex int,
@pagenum int,
@userid int
as
select t.Use_Pic,t.NickName,t.Memo,t.SayContent,t.SayFrom,t.SayDatetime,t.userid,t.SayId,t.Discuss,t.Transmit,
(case when DATEDIFF(SECOND,SayDatetime,GETDATE())<60 then STR(DATEDIFF(SECOND,SayDatetime,GETDATE()))+'秒前'
when DATEDIFF(MINUTE,SayDatetime,GETDATE())<60 then str(DATEDIFF(MINUTE,SayDatetime,GETDATE())) +'分钟前'
when DATEDIFF(HOUR,SayDatetime,GETDATE())<24 then str(DATEDIFF(HOUR,SayDatetime,GETDATE()))+'个小时前'
when DATEDIFF(DAY,SayDatetime,GETDATE())<31 then str(DATEDIFF(DAY,SayDatetime,GETDATE()))+'天前'
when DATEDIFF(MONTH,SayDatetime,GETDATE())<12 then str(DATEDIFF(MONTH,SayDatetime,GETDATE()))+'个月前'
else STR(DATEDIFF(YEAR,SayDatetime,GETDATE())) +'年前'
end) datetime1
from (select ROW_NUMBER() over(order by SayDatetime desc) as ss,ts.SayId,tu.userid,tu.Use_Pic,tu.NickName,tf.Memo,
ts.SayContent,ts.SayDatetime,ts.SayFrom,ts.Discuss,ts.TFPublic,ts.Transmit
from T_FriendList tf join T_User tu on tf.Friend_id=tu.userid join T_Say ts on tf.Friend_id=ts.[User_id]
where ts.[User_id]=@userid) t
where t.ss>(@pageindex-1)*@pagenum and t.ss<=@pageindex*@pagenum order by SayDatetime desc
GO
/****** Object: StoredProcedure [dbo].[PageSina] Script Date: 03/30/2012 18:43:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[PageSina]
@pageindex int,
@pagenum int,
@userid int
as
select t.Use_Pic,t.NickName,t.Memo,t.SayContent,t.SayFrom,t.SayDatetime,t.userid,t.SayId,t.Discuss,t.Transmit,
(case when DATEDIFF(SECOND,SayDatetime,GETDATE())<60 then STR(DATEDIFF(SECOND,SayDatetime,GETDATE()))+'秒前'
when DATEDIFF(MINUTE,SayDatetime,GETDATE())<60 then str(DATEDIFF(MINUTE,SayDatetime,GETDATE())) +'分钟前'
when DATEDIFF(HOUR,SayDatetime,GETDATE())<24 then str(DATEDIFF(HOUR,SayDatetime,GETDATE()))+'个小时前'
when DATEDIFF(DAY,SayDatetime,GETDATE())<31 then str(DATEDIFF(DAY,SayDatetime,GETDATE()))+'天前'
when DATEDIFF(MONTH,SayDatetime,GETDATE())<12 then str(DATEDIFF(MONTH,SayDatetime,GETDATE()))+'个月前'
else STR(DATEDIFF(YEAR,SayDatetime,GETDATE())) +'年前'
end) datetime1
from (select ROW_NUMBER() over(order by SayDatetime desc) as ss,ts.SayId,tu.userid,tu.Use_Pic,tu.NickName,tf.Memo,
ts.SayContent,ts.SayDatetime,ts.SayFrom,ts.Discuss,ts.TFPublic,ts.Transmit
from T_FriendList tf join T_User tu on tf.Friend_id=tu.userid join T_Say ts on tf.Friend_id=ts.[User_id]
where tf.[User_id]=@userid) t
where t.ss>(@pageindex-1)*@pagenum and t.ss<=@pageindex*@pagenum order by SayDatetime desc
GO
/****** Object: StoredProcedure [dbo].[MyCollect] Script Date: 03/30/2012 18:43:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[MyCollect]
@pageindex int,
@pagenum int,
@userid int
as
select userid,Transmit,Discuss,Use_Pic,NickName,SayContent,SayFrom,sayid,case when DATEDIFF(SECOND,CollectDate,GETDATE())<60 then STR(DATEDIFF(SECOND,CollectDate,GETDATE()))+'秒前'
when DATEDIFF(MINUTE,CollectDate,GETDATE())<60 then str(DATEDIFF(MINUTE,CollectDate,GETDATE())) +'分钟前'
when DATEDIFF(HOUR,CollectDate,GETDATE())<24 then str(DATEDIFF(HOUR,CollectDate,GETDATE()))+'个小时前'
when DATEDIFF(DAY,CollectDate,GETDATE())<31 then str(DATEDIFF(DAY,CollectDate,GETDATE()))+'天前'
when DATEDIFF(MONTH,CollectDate,GETDATE())<12 then str(DATEDIFF(MONTH,CollectDate,GETDATE()))+'个月前'
else STR(DATEDIFF(YEAR,CollectDate,GETDATE())) +'年前'
end datetime1 from
(select ROW_NUMBER() over(order by CollectId desc) as ss,Transmit,Discuss,userid,tu.Use_Pic,tu.NickName,SayContent,SayFrom,CollectDate,tc.SayId from T_Collect tc join T_Say ts on tc.SayId=ts.SayId join T_User tu on ts.[User_id]=tu.userid
where CollectUseid=@userid) t where t.ss>(@pageindex-1)*@pagenum and t.ss<=@pageindex*@pagenum
GO
/****** Object: StoredProcedure [dbo].[hfw] Script Date: 03/30/2012 18:43:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[hfw]
@pageindex int,
@pagenum int,
@userid int
as
select * from (select ROW_NUMBER() over(order by Commentid desc) as ss,* from T_Comment tc join T_User tu on tc.SayUserid=tu.userid
where tc.SayUserid=3) t where t.ss>(@pageindex-1)*@pagenum and t.ss<=@pageindex*@pagenum
GO
/****** Object: StoredProcedure [dbo].[dispAllRecord] Script Date: 03/30/2012 18:43:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[dispAllRecord]
@pageindex int,
@pagenum int,
@userid int,
@friendid int
as
select * from (select ROW_NUMBER() over(order by PrivateId desc) as ss,* from (
select NickName,PrivateContent,PrivateDate,Use_Pic,PrivateId from T_privatecontent tp join
T_User tu on tp.Userid=tu.userid where tp.Userid=@userid andfriendid=@friendid
union
select NickName,PrivateContent,PrivateDate,Use_Pic,PrivateId from T_PrivateContent tp join
T_user tu on tp.Userid=tu.userid where tp.Userid=@friendid andfriendid=@userid) t) tt
where tt.ss>(@pageindex-1)*@pagenum and tt.ss<=@pageindex*@pagenum
GO
/****** Object: StoredProcedure [dbo].[disp] Script Date: 03/30/2012 18:43:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[disp]
@userid int,
@friendid int,
@sign int
as
if @sign=1
begin
select * from (select top 5 * from (select NickName,PrivateContent,PrivateDate from T_privatecontent tp join T_User tu on tp.Userid=tu.userid wheretp.Userid=@userid and friendid=@friendid --or friendid=@userid andUserid=@fid
union
select NickName,PrivateContent,PrivateDate from T_PrivateContent tp join T_user tu on tp.Userid=tu.userid wheretp.Userid=@friendid and friendid=@userid) t order by PrivateDate desc) m order by PrivateDate asc
end
else
begin
select * from (select NickName,PrivateContent,PrivateDate from T_privatecontent tp join T_User tu on tp.Userid=tu.userid wheretp.Userid=@userid and friendid=@friendid --or friendid=@userid andUserid=@fid
union
select NickName,PrivateContent,PrivateDate from T_PrivateContent tp join T_user tu on tp.Userid=tu.userid wheretp.Userid=@friendid and friendid=@userid) t order by PrivateDate desc
end
GO
/****** Object: StoredProcedure [dbo].[Comment] Script Date: 03/30/2012 18:43:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[Comment]
@Sayid int
as
select tu.NickName,tu.userid,tu.Use_Pic,tc.CommentContent,tc.Commentid,tc.Sayid,tc.Comment_Useid,
(case when DATEDIFF(SECOND,CommentDate,GETDATE())<60 then STR(DATEDIFF(SECOND,CommentDate,GETDATE()))+'秒前'
when DATEDIFF(MINUTE,CommentDate,GETDATE())<60 then str(DATEDIFF(MINUTE,CommentDate,GETDATE())) +'分钟前'
when DATEDIFF(HOUR,CommentDate,GETDATE())<24 then str(DATEDIFF(HOUR,CommentDate,GETDATE()))+'个小时前'
when DATEDIFF(DAY,CommentDate,GETDATE())<31 then str(DATEDIFF(DAY,CommentDate,GETDATE()))+'天前'
when DATEDIFF(MONTH,CommentDate,GETDATE())<12 then str(DATEDIFF(MONTH,CommentDate,GETDATE()))+'个月前'
else STR(DATEDIFF(YEAR,CommentDate,GETDATE())) +'年前'
end) datetime1 from T_Comment tc join T_User tu on tc.Comment_Useid=tu.userid whereSayid=@Sayid order by tc.CommentDate desc
GO
- SQL数据存储过程
- SQL优化(索引、存储过程、数据分页的存储过程)
- 通过存储过程访问SQL数据
- SQL SERVICE 表数据导出存储过程
- 数据访问类 SQL存储过程
- SQL存储过程动态查询数据区间
- SQL server数据的存储过程
- sql 存储过程 游标执行循环数据
- SQL数据合并复制存储过程
- SQL数据列表移动排序存储过程
- sql server百万数据分页存储过程
- sql存储过程中处理json数据
- sql 存储过程千万条数据分页
- SQL SERVER 2012 T-SQL 发现存储过程元数据
- SQL 数据存储过程 数据访问 用C#编写
- sql存储过程新增数据+返回数据集+事务机制
- 将 SQL Server 存储过程用于数据访问
- 存储过程实现数据导入导出(SQL Server)
- MySQL函数大全及用法示例
- [Linux项目实践] 物联网单板测试之任务三:OLED菜单控制LED
- Linux各发行版本优缺点分析
- 学习三个范式
- C#之关闭显示器!...
- SQL数据存储过程
- 使用C6EZRun开发OMAP平台的DSP内核
- Eclipse常用快捷键(备忘)
- 在ICCAVR中通过使用库函数来避免直接看到敏感算法源码
- 并行计算笔记
- ubuntu下安装显卡GeForce GT440驱动
- Users not mapped!!!
- CF 4D Mysterious Present
- 来看看不同语言程序员的自我优越感