SQL数据存储过程

原创 2012年03月30日 19:07:56

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 and create_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 and friendid=@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 and friendid=@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 where tp.Userid=@userid and friendid=@friendid --or friendid=@userid and Userid=@fid
union
select NickName,PrivateContent,PrivateDate from T_PrivateContent tp join T_user tu on tp.Userid=tu.userid where tp.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 where tp.Userid=@userid and friendid=@friendid --or friendid=@userid and Userid=@fid
union
select NickName,PrivateContent,PrivateDate from T_PrivateContent tp join T_user tu on tp.Userid=tu.userid where tp.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 where Sayid=@Sayid order by tc.CommentDate desc
GO

SQL-更新和删除数据

如何使用UPDATE和DELETE语句进一步操作表数据?
  • beauty_1991
  • beauty_1991
  • 2016年04月29日 13:33
  • 6871

SQL-INSERT-插入数据

INSERT 用来将行插入(或添加)到数据库表。插入有几种方式: 插入完整的行; 插入行的一部分; 插入某些查询的结果。 使用INSERT语句可能需要客户端/服务器DBMS中的特定安全权限。在试图使...
  • beauty_1991
  • beauty_1991
  • 2016年04月27日 22:35
  • 18358

如何使用大数据 SQL 语句

如何使用大数据 SQL 语句标签: SQL, 大数据 从一个庞大的数据库中众多的表格和视图中 query 出所需的数据,是一件熟能生巧的工作。SELECT itemid, itembrand, it...
  • github_37483541
  • github_37483541
  • 2017年02月09日 20:19
  • 391

数据分析中的SQL整理

对于数据分析师来说,做数据分析的前提是有数据,而提取数据就免不了和SQL打交道。SQL语言是数据分析师必须掌握的一项基本技能,它可以衡量一个数据分析师对数据查询和获取的能力,否则就只能等着别人把数据送...
  • moguxiansheng1106
  • moguxiansheng1106
  • 2015年03月14日 12:43
  • 4287

SQL 筛选出最新一条数据

sql中exists,not exists的用法 exists : 强调的是是否返回结果集,不要求知道返回什么, 比如:   select name from st...
  • THX187
  • THX187
  • 2016年12月09日 17:57
  • 1565

SQL 数据排重,去掉重复数据

最大的错误:     在对数据排重的时候,首先想到的就是Distinct,虽然这很管用,但多数场合下不适用,因为通常排重后还要做进一步处理,比如对编号排重后要按日期统计等。     无法排重的Gro...
  • cuiyan1982
  • cuiyan1982
  • 2015年05月18日 09:12
  • 10874

sqlserver 查询当日、当月数据

--查询当天:    select * from info where DateDiff(dd,datetime,getdate())=0       --查询24小时内的:    s...
  • wd4java
  • wd4java
  • 2016年02月19日 15:37
  • 8821

sql 查出一张表中重复的所有记录数据

1、在面试的时候碰到一个 问题,就是让写一张表中有id和name 两个字段,查询出name重复的所有数据,现在列下: select * from xi a where (a.username...
  • A11085013
  • A11085013
  • 2013年01月28日 15:42
  • 139741

SQL 2008R2 误删除数据恢复方法(一)

原文出处:http://blog.csdn.net/dba_huangzj/article/details/8491327 本来想记录一下利用recovery for sql  server 这个软件...
  • qyx0714
  • qyx0714
  • 2017年06月20日 11:52
  • 1091

sql 数据分析

第一部分、十道海量数据处理面试题   1、海量日志数据,提取出某日访问百度次数最多的那个IP。   此题,在我之前的一篇文章算法里头有所提到,当时给出的方案是:IP的数目还是有限的,最多2...
  • chenglin1102
  • chenglin1102
  • 2013年12月28日 12:44
  • 1016
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL数据存储过程
举报原因:
原因补充:

(最多只允许输入30个字)