操作类的一些SQL 储存过程

2008-01-16 16:46

--用户注册信息

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
create proc [dbo].[proc_Register]
@MemberName varchar(20),
@nickname varchar(20),
@Password varchar(128),
@Question nvarchar(20),
@Answer nvarchar(20)
as
begin
BEGIN TRANSACTION   --开始事务
declare @MemberNumber int
    ,@MemberID   uniqueidentifier
    ,@GroupID   uniqueidentifier

if( exists( select * from MemberInfo
     where MemberName = @MemberName))
begin
   ROLLBACK
   return 1001   --已经存在该用户
end
else
begin
   select @MemberID = newid()
   select @MemberNumber =cast( floor(rand()*10000000) as int)
   while (len(@MemberNumber) >= 6 and exists( select * from [FriednDB].[dbo].[MemberInfo]
     where MemberNumber = @MemberNumber))
   begin
    select @MemberNumber = cast( floor(rand()*10000000) as int)
   end
   --Member库的会员信息
   INSERT INTO [BuygoMember].[dbo].[MemberInfo]
           ([MemberName]
           ,[Password]
           ,[Address]
           ,[Sex]
           ,[Birthday]
           ,[Question]
           ,[Answer]
           ,[NativePlace])
   VALUES
           (@MemberName
           ,@Password
           ,1
           ,null
           ,null
           ,@Question
           ,@Answer
           ,1)

   if(@@rowcount <> 1)
   begin
    ROLLBACK
    return -1000 --注册失败
   end
   --Friend 表的会员信息
   INSERT INTO [FriednDB].[dbo].[MemberInfo]
           ([MemberID]
           ,[MemberName]
           ,[nickname]
           ,[ClickValue]
           ,[FirstTime]
           ,[LastTime]
           ,[DiskSize]
           ,[Keywords]
           ,[MemberNumber])
   VALUES
           (@MemberID
           ,@MemberName
           ,@nickname
           ,0
           ,getdate()
           ,getdate()
           ,10
           ,''
           ,@MemberNumber)

   if(@@rowcount <> 1)
   begin
    ROLLBACK
    return -1000 --注册失败
   end
   select @GroupID = newid()

   --图片的基本信息
   INSERT INTO [FriednDB].[dbo].[PictureGroup]
           ([GroupID]
           ,[MemberID]
           ,[GroupName])
   VALUES
           (@GroupID
           ,@MemberID
           ,'_Picture')   --生成默认文件名

   if(@@rowcount <> 1)
   begin
    ROLLBACK
    return -1000 --注册失败
   end

   INSERT INTO [FriednDB].[dbo].[Picture]
           ([PictureID]
           ,[GroupID]
           ,[PictureName]
           ,[Depict]
           ,[UpDateTime]
           ,[FileSize])
   VALUES
      (newid()
      ,@GroupID
      ,'defaultpicture.gif'
      ,''
      ,getdate()
      ,0)

   if(@@rowcount <> 1)
   begin
    ROLLBACK
    return -1000 --注册失败
   end

   INSERT INTO [FriednDB].[dbo].[PictureGroup]
      ([GroupID]
      ,[MemberID]
      ,[GroupName])
   VALUES
      (newid()
      ,@MemberID
      ,'生活照')

   if(@@rowcount <> 1)
   begin
    ROLLBACK
    return -1000 --注册失败
   end

INSERT INTO [FriednDB].[dbo].[FriendGroup]
           ([GroupID]
           ,[MemberID]
           ,[GroupName])
     VALUES
           (newid()
           ,@MemberID
           ,'我的好友')

   if(@@rowcount <> 1)
   begin
    ROLLBACK
    return -1000 --注册失败
   end

   INSERT INTO [FriednDB].[dbo].[FriendGroup]
      ([GroupID]
      ,[MemberID]
      ,[GroupName])
   VALUES
      (newid()
      ,@MemberID
      ,'黑名单')

   if(@@rowcount <> 1)
   begin
    ROLLBACK
    return -1000 --注册失败
   end

   INSERT INTO [BuygoMember].[dbo].[ContactMode]
           ([MemberName]
           ,[Other])
   VALUES
           (@MemberName
           ,'')

   if(@@rowcount <> 1)
   begin
    ROLLBACK
    return -1000 --注册失败
   end

   SELECT bmi.[MemberName]
    ,[CityID]
    ,[Sex]
    ,[MemberID]
    ,[nickname]
    ,[MemberNumber]
    ,[ProvinceName]
    ,[CityName]
    FROM [BuygoMember].[dbo].[MemberInfo] as bmi
    ,[FriednDB].[dbo].[MemberInfo] as fmi
    ,[BuygoMember].[dbo].[City]
    ,[BuygoMember].[dbo].[province]
    where bmi.MemberName = fmi.MemberName
    and CityID = Address
    and [BuygoMember].[dbo].[City].ProvinceID = [BuygoMember].[dbo].[province].ProvinceID
end
if(@@error <> 0)
begin
   ROLLBACK
   return -1000 --注册失败
end
else
begin
   COMMIT
   return 1007   --注册成功
end
end

--添加联系方式储存过程

reate proc proc_EditContactMode
@MemberName varchar(20)
,@MobilePhone char(11)
,@Telephone char(13)
    ,@QQ char(11)
    ,@MSN varchar(30)
    ,@EMail varchar(30)
    ,@Paopao varchar(30)
    ,@Other varchar(50)
    ,@ICQ varchar(15)
as
begin
UPDATE [BuygoMember].[dbo].[ContactMode]
   SET [MobilePhone] = @MobilePhone
    ,[Telephone] = @Telephone
    ,[QQ] = @QQ
    ,[MSN] = @MSN
    ,[E-Mail] = @EMail
    ,[Paopao] = @Paopao
    ,[Other] = @Other
    ,[ICQ] = @ICQ
WHERE [MemberName] = @MemberName

if(@@error <> 0)
   return -1000 -- 出错
else
   return 1008   -- 修改成功
end

--=================================================
--//查看用户的基本信息和联系方式
--=================================================
create proc proc_SEL_BaseMemberInfo
@MemberName varchar(20) = ''
as
begin
if(@MemberName = '')
begin
select member.[MemberName]
    ,[CityName]
    ,[ProvinceName]
    ,[Sex]
    ,[Birthday]
    ,[MobilePhone]
    ,[Telephone]
    ,[QQ]
    ,[MSN]
    ,[E-Mail]
    ,[Paopao]
    ,[Other]
    ,[ICQ]
from [BuygoMember].[dbo].[MemberInfo] as member,
    [BuygoMember].dbo.ContactMode as contact,
    [BuygoMember].dbo.City,
    [BuygoMember].dbo.province
where member.[MemberName] = contact.[MemberName]
    and member.NativePlace = City.CityID
    and City.ProvinceID = province.ProvinceID
end
select member.[MemberName]
    ,[CityName]
    ,[ProvinceName]
    ,[Sex]
    ,[Birthday]
    ,[MobilePhone]
    ,[Telephone]
    ,[QQ]
    ,[MSN]
    ,[E-Mail]
    ,[Paopao]
    ,[Other]
    ,[ICQ]
from [BuygoMember].[dbo].[MemberInfo] as member,
    [BuygoMember].dbo.ContactMode as contact,
    [BuygoMember].dbo.City,
    [BuygoMember].dbo.province
where member.[MemberName] = contact.[MemberName]
    and member.NativePlace = City.CityID
    and City.ProvinceID = province.ProvinceID
    and member.[MemberName] = @MemberName
end

SELECT [MemberID]
      ,bmi.[MemberName]
      ,[nickname]
      ,[FriendAim]
      ,[Wedlock]
      ,[ClickValue]
      ,[FirstTime]
      ,[LastTime]
      ,[DiskSize]
      ,[Keywords]
      ,[MemberNumber]
      ,[Password]
      ,[NativePlace]
      ,[Sex]
      ,[Birthday]
      ,[Question]
      ,[Answer]
FROM [FriednDB].[dbo].[MemberInfo] as fmi,[BuygoMember].[dbo].[MemberInfo] as bmi
where bmi.[MemberName] = fmi.[MemberName]