根据条件进行判断事务存储过程

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

 

 

-- =============================================
-- Author:  <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[prop_DownloadManage]
 -- Add the parameters for the stored procedure here
 @UID INT,
 @RID INT,
 @Point INT,
 @Rpro int
 
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 DECLARE @URGID INT,@Points INT,@UTID INT,@Rp bit,@DLC INT
 SET NOCOUNT ON;

    -- Insert statements for procedure here
   BEGIN TRAN
    SELECT     @URGID = dbo.UserDes.URGID--查询时前台注册还是市场部人员开通的用户
  FROM         dbo.[User] INNER JOIN
        dbo.UserDes ON dbo.[User].UID = dbo.UserDes.UID
  WHERE     (dbo.[User].UID = @UID)
  IF @@ERROR<> 0
  BEGIN
   ROLLBACK TRAN;
   RETURN -1;
  END
  
  IF @URGID = 1 --市场部开通用户
  BEGIN
   SELECT @UTID = dbo.User_Type.UTID--查询用户类型
   FROM   dbo.[User] INNER JOIN
       dbo.UserDes ON dbo.[User].UID = dbo.UserDes.UID INNER JOIN
       dbo.User_Type ON dbo.UserDes.UTID = dbo.User_Type.UTID
   WHERE  (dbo.[User].UID = @UID);
   
   IF @@ERROR<> 0
   BEGIN
    ROLLBACK TRAN;
    RETURN -1;
   END
   IF @UTID < 3 -- 1,2为集团用户
   BEGIN
    IF @UTID = 1 --如果是试用用户最多下载500次资源
    BEGIN
     SELECT     @DLC  = DownloadCount
     FROM         dbo.UserDes
     WHERE     (UID = @UID)
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN
      RETURN -1
     END
     IF @DLC > 500
     BEGIN
      COMMIT TRAN
      RETURN -2
     END
    END

    IF @Rpro = 1
    BEGIN
     SELECT @Rp = RPro_1
     FROM    dbo.[User] INNER JOIN
       dbo.User_Category ON dbo.[User].UID = dbo.User_Category.UID
     WHERE (dbo.[User].UID = @UID)
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN
      RETURN -1
     END
    END 
    ELSE IF @Rpro = 2
    BEGIN
     SELECT @Rp = RPro_2
     FROM    dbo.[User] INNER JOIN
       dbo.User_Category ON dbo.[User].UID = dbo.User_Category.UID
     WHERE (dbo.[User].UID = @UID)
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN
      RETURN -1
     END
    END 
    ELSE IF @Rpro = 3
    BEGIN
     SELECT @Rp = RPro_3
     FROM    dbo.[User] INNER JOIN
       dbo.User_Category ON dbo.[User].UID = dbo.User_Category.UID
     WHERE (dbo.[User].UID = @UID)
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN
      RETURN -1
     END
    END 
    ELSE IF @Rpro = 4
    BEGIN
     SELECT @Rp = RPro_4
     FROM    dbo.[User] INNER JOIN
       dbo.User_Category ON dbo.[User].UID = dbo.User_Category.UID
     WHERE (dbo.[User].UID = @UID)
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN
      RETURN -1
     END
    END
    ELSE IF @Rpro = 5
    BEGIN
     SELECT @Rp = RPro_5
     FROM    dbo.[User] INNER JOIN
       dbo.User_Category ON dbo.[User].UID = dbo.User_Category.UID
     WHERE (dbo.[User].UID = @UID)
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN
      RETURN -1
     END
    END
    ELSE IF @Rpro = 6
    BEGIN
     SELECT @Rp = RPro_6
     FROM    dbo.[User] INNER JOIN
       dbo.User_Category ON dbo.[User].UID = dbo.User_Category.UID
     WHERE (dbo.[User].UID = @UID)
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN
      RETURN -1
     END
    END 
    ELSE IF @Rpro = 7
    BEGIN
     SELECT @Rp = RPro_7
     FROM    dbo.[User] INNER JOIN
       dbo.User_Category ON dbo.[User].UID = dbo.User_Category.UID
     WHERE (dbo.[User].UID = @UID)
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN
      RETURN -1
     END
    END 
    ELSE IF @Rpro = 8
    BEGIN
     SELECT @Rp = RPro_8
     FROM    dbo.[User] INNER JOIN
       dbo.User_Category ON dbo.[User].UID = dbo.User_Category.UID
     WHERE (dbo.[User].UID = @UID)
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN
      RETURN -1
     END
    END  
    ELSE IF @Rpro = 9
    BEGIN
     SELECT @Rp = RPro_9
     FROM    dbo.[User] INNER JOIN
       dbo.User_Category ON dbo.[User].UID = dbo.User_Category.UID
     WHERE (dbo.[User].UID = @UID)
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN
      RETURN -1
     END
    END  
    ELSE IF @Rpro = 10
    BEGIN
     SELECT @Rp = RPro_10
     FROM    dbo.[User] INNER JOIN
       dbo.User_Category ON dbo.[User].UID = dbo.User_Category.UID
     WHERE (dbo.[User].UID = @UID)
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN
      RETURN -1
     END
    END 
    ELSE IF @Rpro = 11
    BEGIN
     SELECT @Rp = RPro_11
     FROM    dbo.[User] INNER JOIN
       dbo.User_Category ON dbo.[User].UID = dbo.User_Category.UID
     WHERE (dbo.[User].UID = @UID)
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN
      RETURN -1
     END
    END 
    ELSE IF @Rpro = 12
    BEGIN
     SELECT @Rp = RPro_12
     FROM    dbo.[User] INNER JOIN
       dbo.User_Category ON dbo.[User].UID = dbo.User_Category.UID
     WHERE (dbo.[User].UID = @UID)
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN
      RETURN -1
     END
    END 
    ELSE IF @Rpro = 13
    BEGIN
     SELECT @Rp = RPro_13
     FROM    dbo.[User] INNER JOIN
       dbo.User_Category ON dbo.[User].UID = dbo.User_Category.UID
     WHERE (dbo.[User].UID = @UID)
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN
      RETURN -1
     END
    END 

    
    IF @Rp = 0 -- 没有权限下载
    BEGIN
     COMMIT TRAN
     RETURN -3 
    END
    ELSE IF @Rp = 1 --有权限下载
    BEGIN
     --增加下载次数
     UPDATE [SVE].[dbo].[UserDes]
     SET [DownloadCount] = (DownloadCount + 1)      
     WHERE [UID] = @UID
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN
      RETURN -1
     END
     --插入下载信息表
     INSERT INTO [SVE].[dbo].[R_DownloadInfo]
         ([RID]
         ,[UID])
      VALUES
         (@RID,@UID)
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN
      RETURN -1
     END
     ROLLBACK TRAN
     RETURN 1
    END
   END
   ELSE  --计点用户
   BEGIN
    SELECT @Points = dbo.UserDes.Points
    FROM   dbo.[User] INNER JOIN
       dbo.UserDes ON dbo.[User].UID = dbo.UserDes.UID
    WHERE  (dbo.[User].UID = @UID);
    IF @@ERROR <> 0
    BEGIN
     ROLLBACK TRAN;
     RETURN -1;
    END
   
    IF @Points >= @Point
    BEGIN
     UPDATE [SVE].[dbo].[UserDes]
     SET [Points] = (Points - @Point),[DownloadCount] = (DownloadCount + 1)      
     WHERE [UID] = @UID
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN;
      RETURN -1;
     END
     INSERT INTO [SVE].[dbo].[R_DownloadInfo]
         ([RID]
         ,[UID])
      VALUES
         (@RID,@UID)
     IF @@ERROR <> 0
     BEGIN
      ROLLBACK TRAN
      RETURN -1
     END
     COMMIT TRAN
     RETURN 1
    END
    ELSE
    BEGIN
     COMMIT TRAN
     RETURN 0     
    END
   END
   
  END 
  ELSE IF @URGID = 2 --前台注册用户
  BEGIN
   SELECT @Points = dbo.UserDes.Points
   FROM   dbo.[User] INNER JOIN
       dbo.UserDes ON dbo.[User].UID = dbo.UserDes.UID
   WHERE  (dbo.[User].UID = @UID);
   IF @@ERROR <> 0
   BEGIN
    ROLLBACK TRAN;
    RETURN -1;
   END
   
   IF @Points >= @Point
   BEGIN
    UPDATE [SVE].[dbo].[UserDes]
    SET [Points] = (Points - @Point),[DownloadCount] = (DownloadCount + 1)      
    WHERE [UID] = @UID
    IF @@ERROR <> 0
    BEGIN
     ROLLBACK TRAN;
     RETURN -1;
    END
    INSERT INTO [SVE].[dbo].[R_DownloadInfo]
        ([RID]
        ,[UID])
     VALUES
        (@RID,@UID)
    IF @@ERROR <> 0
    BEGIN
     ROLLBACK TRAN
     RETURN -1
    END
    COMMIT TRAN
    RETURN 1
   END
   ELSE
   BEGIN
    COMMIT TRAN
    RETURN 0    
   END
   
  END 
  
  
   
   
END