22,209
社区成员
发帖
与我相关
我的任务
分享
CREATE PROC [dbo].[P_Admin_GoldSell_OfflinePay]
@serialno varchar(50)
AS
BEGIN
DECLARE @userID VARCHAR(50);
DECLARE @status INT;
DECLARE @number DECIMAL(18,3);
DECLARE @user_Balance DECIMAL(18,3);
SELECT @userID = userID, @number = number,@status = [status] FROM fr_cus_gold_sellLog WHERE serialno = @serialno;
SELECT @user_Balance = balance_free FROM fr_customer WHERE userID = @userID ;
select 'test' return;
END
SELECT @userID = userID, @number = number,@status = [status] FROM fr_cus_gold_sellLog WHERE serialno = @serialno;
SELECT @user_Balance = balance_free FROM fr_customer WHERE userID = @userID ;
SELECT @userID = userID FROM fr_cus_gold_sellLog WHERE serialno = '201505180000000001'; --@serialno
--SELECT @user_Balance = balance_free FROM fr_customer WHERE userID = @userID ;
SELECT @serialno+'----'+@userID;
ALTER PROC [dbo].[P_Admin_GoldSell_OfflinePay]
@serialno varchar(50)
AS
BEGIN
DECLARE @userID VARCHAR(50);
DECLARE @status INT;
DECLARE @number DECIMAL(18,3);
DECLARE @user_Balance DECIMAL(18,3);
SELECT @userID = userID, @number = number,@status = [status] FROM fr_cus_gold_sellLog WHERE serialno = @serialno;
SELECT @user_Balance = balance_free FROM fr_customer WHERE userID = @userID ;
IF @status != 1
BEGIN
SELECT 'COMPLETED';
RETURN;
END
IF @user_Balance - @number > 0 --判断用户金币余额的65%减去要提现的金币是否大于0
BEGIN
PRINT '足额'
DECLARE @ROWCOUNT INT;
SET @ROWCOUNT = 0;
BEGIN TRAN;
UPDATE fr_cus_gold_sellLog SET [status] = 2 WHERE serialno = @serialno;
SET @ROWCOUNT = @ROWCOUNT + @@ROWCOUNT;
--UPDATE fr_customer SET balance_free = balance_free - @number WHERE userID = @userID;
--SET @ROWCOUNT = @ROWCOUNT + @@ROWCOUNT;
IF @ROWCOUNT = 1
BEGIN
COMMIT TRAN;
SELECT 1;
END
ELSE
BEGIN
ROLLBACK TRAN;
SELECT 0;
END
END
ELSE
BEGIN
UPDATE fr_cus_gold_sellLog SET [status] = 3 WHERE serialno = @serialno;
SELECT 'NOT_ENOUGH'; --金币余额卖出不足
END
END
通过C#调用存储过程,问题出在这句:IF @user_Balance - @number > 0过不去,直接就跳到:SELECT 'NOT_ENOUGH'; --金币余额卖出不足
直接在数据库中执行储存过程:exec [P_Admin_GoldSell_OfflinePay] '201507160000000001' (注:@serialno=201507160000000001)运行到 IF @user_Balance - @number > 0这句是没问题的,因为表中的数据@user_Balance确实大于@number
CREATE PROC [dbo].[P_Admin_GoldSell_OfflinePay](
@serialno varchar(50),
@userID VARCHAR(50) output,
@status INT output,
@number DECIMAL(18,3) output,
@user_Balance DECIMAL(18,3) output)
AS
BEGIN
SELECT @userID = userID, @number = number,@status = [status]
FROM fr_cus_gold_sellLog
WHERE serialno = @serialno;
SELECT @user_Balance = balance_free
FROM fr_customer WHERE userID = @userID ;
END
或者
CREATE PROC [dbo].[P_Admin_GoldSell_OfflinePay]
(@serialno varchar(50))
AS
BEGIN
DECLARE @userID VARCHAR(50);
DECLARE @status INT;
DECLARE @number DECIMAL(18,3);
DECLARE @user_Balance DECIMAL(18,3);
SELECT @userID = userID, @number = number, @status = [status]
FROM fr_cus_gold_sellLog
WHERE serialno = @serialno;
SELECT @user_Balance = balance_free
FROM fr_customer WHERE userID = @userID ;
select @userID as userid, @number as number,
@status as status, @user_Balance as user_Balance;
END