一些sql语句

-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        jinho
-- Create date: 2010年3月21日10:09:11
-- Description:    互相添加好友 并删除该条信息
-- =============================================
CREATE PROCEDURE AddFriend
    @myId int = 0,
    @myFriend varbinary(max) , --我的好友
    @friendId int  =0,
    @friendFriend varbinary(max), --朋友的好友
    @reqMsgId int = 0   --消息ID
AS
BEGIN
    SET NOCOUNT OFF;
    declare @error int;
    set @error = 0; --记录错误数
    begin tran
        update userinfo set Friends =@myFriend  where id =@myId ; --修改我的好友
        set @error = @@error;
        update userinfo set Friends =@friendFriend  where id =@friendId ; --修改朋友的ID
        set @error = @error+@@error;
        /*delete [Messages] where id = @reqMsgId;
        set @error = @error+@@error;
        delete [MessageReply] where messageId=@reqMsgId;
        set @error = @error+@@error;*/
        exec DeleteMsgAndReplyMsg @reqMsgId;
        if(@error=0)
            commit tran;
        else
            rollback tran;
END
GO

-- =============================================
-- Author:        jinho
-- Create date: 2010年3月21日10:21:52
-- Description:    删除消息和消息的回复信息
-- =============================================
CREATE PROCEDURE DeleteMsgAndReplyMsg
    @reqMsgId int = 0 --消息ID
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    declare @error int;
    set @error = 0; --记录错误数
    begin tran
        delete [Messages] where id = @reqMsgId;
        set @error = @error+@@error;
        delete [MessageReply] where messageId=@reqMsgId;
        set @error = @error+@@error;
        if(@error=0)
            commit tran;
        else
            rollback tran;
END
GO

---test
DeleteMsgAndReplyMsg 16

 

-- =============================================
-- Author:        jinho
-- Create date: 2010年3月20日12:17:21
-- Description:    获取交谈信息和组信息 个数
-- =============================================
CREATE PROCEDURE GetMsgCount
     @userId int = 0, --用户ID
     @msgType int = 0, --0:requestMsg 1:converseMsg+groupmsg
     @returnValue int output --输出参数
AS
BEGIN
    SET NOCOUNT ON;
    declare @msgcount int --交谈消息
    declare @groupmsgCount int --组消息
    if(@msgType = 1) --如果消息类型为converseMsg ,则显示 交谈消息和组消息
    begin
        set @msgcount= (SELECT COUNT(*) FROM [MESSAGES] WHERE toId = @userId AND msgType = @msgType AND isRead = 0)
        set @groupmsgCount = (select count(*) from [groupSendMessage] where toUserId = @userId AND stauts = 0 )
        set @returnValue = (@msgcount+@groupmsgCount)
    end
    else  --显示请求消息
    begin
        set @returnValue=(SELECT COUNT(*) FROM [MESSAGES] WHERE toId = @userId AND msgType = @msgType)
    end
END
GO

---test
declare @count int
exec GetMsgCount 17,0 ,@count OUTPUT
print @count
go