存储过程报错Procedure execution failed 42000 - [SQL Server]必须声明标量变量

Question

sql server 2008存储过程标量变量

Answer 1

EXEC的时候报错，不一定是存储过程内的问题，检查一下调用存储过程的语句。

Answer 2

ALTER PROCEDURE [dbo].[Pro_CD_Recharge_SP]
    @UserCardNo VARCHAR(16), 
    @RechargeAmount decimal(18,2),                                          
    @Rechargeway varchar(30),   
    @Cardtype varchar(4),   
    @Terminalno varchar(10),  
    @Operator varchar(20),         
    @Guid varchar(32),
    @Success int OUTPUT,
    @BankAccount varchar(30)
AS
    DECLARE @MoneyRow int                      
    DECLARE @Nowdate date                                
    DECLARE @NowDateTime datetime                         
    DECLARE @RemainAfter decimal(18,2)            
    DECLARE @TableName varchar(50);                
    DECLARE @ExeSql varchar(1000);             
    declare @OrderNo varchar(8);
    
    SET @MoneyRow = 0                             
    SET @Nowdate = CONVERT(varchar(10),GETDATE(),120)           
    SET @OrderNo=CONVERT(varchar(10),GETDATE(),112)
    SET @NowDateTime = GETDATE()                                        
    SET @Success = 0
BEGIN
    SET NOCOUNT ON; 
    SET XACT_ABORT ON;  
    BEGIN TRY
        BEGIN Tran
            select @MoneyRow = COUNT(FUserCardNo) from DebitRecord where OrderNo=@OrderNo and Status=0 and BankAccount=@BankAccount and FUserCardNo=@UserCardNo
            IF @MoneyRow = 1
BEGIN
SET @MoneyRow = 0
SELECT @MoneyRow = COUNT(FUserCardNo) FROM CD_Money WITH (XLOCK,ROWLOCK) WHERE FUserCardNo = @UserCardNo
IF @MoneyRow = 1
  BEGIN
UPDATE CD_Money WITH(XLOCK,ROWLOCK) SET FAddPurse=isnull(FAddPurse,0)+@RechargeAmount,FResidualAmount=isnull(FResidualAmount,0)+@RechargeAmount,FLastTime = @NowDateTime WHERE FUserCardNo = @UserCardNo;
                
SELECT @RemainAfter=FAddPurse+isnull(FAddBenefit,0)-isnull(FSumMoney,0) FROM CD_Money WITH (XLOCK,ROWLOCK) WHERE FUserCardNo = @UserCardNo
INSERT INTO CD_RechargeDetail(FGUID,FUserCardNO,FCAmount,FAmount,FTradeNo,FRechargeWay,FOperator,FOperateDateTime)
VALUES(@Guid,@UserCardNo,@RechargeAmount,@RemainAfter,@Terminalno,@Rechargeway,@Operator,@NowDateTime);
                
update DebitRecord set Status=1,EndTime=GETDATE() where OrderNo=@OrderNo and Status=0 and BankAccount=@BankAccount and FUserCardNo=@UserCardNo
                   
SET @TableName ='CD_T' + CONVERT(varchar(100), @NowDate, 112);
SET @ExeSql = 'insert into ' 
+ @TableName
+ '('
+ 'FGUID,FTime,FUserCardNo,FCardType,FBizType,FPurseMoney,FPurseGas,FPurseAmount,FMoney,FBenefits,FOperatorCardNo,FDirector'
+ ')values('''
+ @Guid
+ ''',''' + CONVERT(varchar(100), @NowDateTime, 120)
+ ''',''' + @UserCardNo
+ ''',''' + @CardType
+ ''',''' + '1016'
+ ''',''' + '0.00'
+ ''',''' + '0.00'
+ ''',''' + CONVERT(VARCHAR(15),@RemainAfter)
+ ''',''' + CONVERT(VARCHAR(15),@RechargeAmount)
+ ''',''' + '0.00'
+ ''',''' + @Operator
+ ''',''' + '1'+ ''');';
                        print @exesql  --将动态语句print出来看看
EXEC(@ExeSql);      
SET @Success = 1;
  END
 END
ELSE
SET @Success = -2;
        COMMIT Tran
    END TRY
    BEGIN CATCH
        IF XACT_STATE() = -1 
            ROLLBACK Tran;
    END CATCH
    SET NOCOUNT OFF
END

Answer 3

Quote: 引用 2 楼 shinger126 的回复:

                        ......
                        print @exesql  --将动态语句print出来看看
        .....和添加这句之前一样的错误
应该是还程序还没有运行到这里就报错了吧