求解存储过程中接收参数过剩

求解存储过程中接收参数过多
如题，我的存储过程定义的参数是 varchar(8000),但是从程序过来的参数已经超过 8000 了
一下是我的存储过程,求高手解答


      ALTER PROCEDURE [dbo].[QDZYExportCostExcel_EX]
     @BusinessNum varchar(8000),
       @Consignor varchar(100), 
            @StartExportDate datetime, 
     @EndExportDate datetime, 
     @ExportMan  varchar(50), 
    @ExportType varchar(20), 
     @InputMan varchar(50),       
          @InputDepartment varchar(20), 
           @StartManagerCheckCostDate  datetime, 
     @EndManagerCheckCostDate datetime, 
   @Flag_ManagerCheckCost varchar(1), 
       @DeliveryListNum varchar(50),   
 @AdvanceNum varchar(20), 
     @ApproveNum varchar(20),   
    @VoyageNum  varchar(50),
      @TotalNumvarchar(12),
      @DocumentNum varchar(50),   
    @ContainerNum varchar(50),     
  @Flag_Customs varchar(1),    
  @EndInputDate datetime , 
     @StartInputDate datetime           
AS 
   BEGIN 
   DECLARE @sql VARCHAR(8000) 
   SET @sql = 'select
     Convert(varchar(10),ccd.DeclareDate,120) [申报日期(业务日期)],
        ccd.Operate_Name    [经营单位（客户自编号）], 
   ccd.ConveyanceName[船名], 
   ccd.VoyageNum[航次], 
   ccd.DeliveryListNum [提单号], 
   ccd. CustomsNum [海关编码],
    ''''[随附单证号（报检号）],   
 ccd.GoodsCount[件数],  
  ccd.GW[毛重],   
 ''''[品名（货物属性描述）],  
  case cfi.CostType when ''0'' then ''AR'' else ''AP'' end [应收/应付],    
cfi.Code[客户/供应商(SAP ID)],    
cfi.ChargeCode[Charge Code],    
cfi.Currency_Id[币种],   
 (case cfi.CostType when ''0'' then (RealMoney) else (RealMoney) end ) [金额],   
 (case ccd.Flag_Customs when ''0'' then ''OB'' else ''IB'' end)[进出口],  
  '''' [始发港],    
    '''' [目的港],    
''''[SVVD],   
 ccd.InputMan[销售员],    
''''[接货地城市],    
'''' [目的地城市],    
''''[开航日期],  
  ''''[报检员(责任人)],    
''''[验货员],    
'''' [单证说明（备注）],   
 ''''[运输工具名称],    
''''[包装种类],    
'''' [贸易方式],    
''''[合同协议号],    
''''[集装箱号],    
''''[到货日期（抵港日期）],    
''''[放行日期],    
ccd.ApproveNum[核销单号],    
ccd.RecordNum[手册号],    
ccd.PermitNum [许可证号],    
  ''''[二程提单号],    
(case cfi.CostType when ''0'' then cfi.Code else '''' end)[委托人],   
 cfi.Flag_ManagerCheckCost[是否审核],   
 Convert(varchar(1),cfi.IsExport)[是否导出]  
    from Cost_FeeInfo as cfi 
   left join bus_BusinessDetail as bbd on cfi.BusinessNum = bbd.BusinessNum 
   left join customs_CustomsDeclaration as ccd on ccd.BusinessNum = cfi.BusinessNum 
   where 1 = 1 and cfi.Flag_ManagerCheckCost = ''1'' and ccd.IsAvailable = ''1'' '  
  IF @BusinessNum IS NOT NULL 
   BEGIN  
          SET @sql = @sql + ' and cfi.BusinessNum in( '''+replace(@BusinessNum,',',''',''')+''')'     
END    

   IF @Consignor IS NOT NULL   
 BEGIN     
 
SET @SQL = @SQL + ' AND Consignor = '''+@Consignor+''''   
 END        

  IF @InputMan IS NOT NULL    
BEGIN   
 SET @SQL = @SQL + ' AND cfi.InputMan = '''+@InputMan+''''    
END     

 IF @InputDepartment IS NOT NULL  
  BEGIN    
        SET @SQL = @SQL + ' AND cfi.InputDepartment = '''+@InputDepartment+''''   
 END      

   IF @ExportType IS NOT NULL   
          BEGIN  
             SET @sql = @sql + ' AND cfi.IsExport = '''+@ExportType+''''  
         END         

      IF @StartExportDate IS NOT NULL AND @EndExportDate IS NOT NULL   
       BEGIN         
     SET @SQL = @SQL + ' AND cfi.ExportDate BETWEEN '''+CONVERT(VARCHAR(50),@StartExportDate,120)        
                      + ''' AND ''' + CONVERT(VARCHAR(50),@EndExportDate,120)+''''    
      END       

IF @ExportMan IS NOT NULL  
        BEGIN      
        SET @SQL = @SQL + ' AND cfi.ExportMan = '''+@ExportMan+''''    
      END   

      IF @StartManagerCheckCostDate IS NOT NULL AND @EndManagerCheckCostDate IS NOT NULL   
       BEGIN   
           SET @SQL = @SQL + ' AND ManagerCheckCostDate BETWEEN '''+CONVERT(VARCHAR(50),@StartManagerCheckCostDate,120) 
                           + ''' AND ''' + CONVERT(VARCHAR(50),@EndManagerCheckCostDate,120)+''''  
        END     

IF @AdvanceNum IS NOT NULL   
 BEGIN    
SET @SQL = @SQL + ' AND ccd.AdvanceNum = ''' +@AdvanceNum + ''''  
  END      

  IF @ApproveNum IS NOT NULL    
BEGIN     
SET @SQL = @SQL + ' AND ccd.ApproveNum = ''' +@ApproveNum+ ''''  
  END    

    IF @VoyageNum IS NOT NULL    
BEGIN     
SET @SQL = @SQL + ' AND ccd.VoyageNum = ''' +@VoyageNum+''''    
END    

  IF @TotalNum IS NOT NULL    
BEGIN    
SET @SQL = @SQL + ' AND ccd.TotalNum = '''+@TotalNum+''''    
    END     

   IF @ContainerNum IS NOT NULL    
BEGIN     
SET @SQL = @SQL + ' AND ccd.TotalNum IN (SELECT TotalNum FROM customs_Container WHERE C_ContainerNum = ''' + @ContainerNum + ''')'     
     END     

     IF @DocumentNum IS NOT NULL     
       BEGIN                      
    SET @SQL = @SQL + ' AND ccd.TotalNum IN (SELECT TotalNum FROM customs_Document WHERE (DocumentsCode = ''A'' OR DocumentsCode = ''B'') AND DocumentsNum = ''' + @DocumentNum + ''')'      
   END    

  IF @Flag_Customs IS NOT NULL     
 
BEGIN         
SET @SQL = @SQL + ' AND ccd.Flag_Customs = '''+@Flag_Customs+''''    
END      

    IF @BusinessNum IS NOT NULL      
      BEGIN             
       SET @SQL=@SQL+' AND bbd.BusinessNum  in ( '''+replace(@BusinessNum,',',''',''')+''')'        
    END     
      print (@sql)   
 exec(@sql)  
  END

[解决办法]
把 DECLARE @sql VARCHAR(8000)

改为： DECLARE @sql VARCHAR(max) 试试
[解决办法]
你的问题在于，由于你的拼接语句中包含的字符太多，超过了你上面定义的varchar(8000),所以才报错的
[解决办法]
另外，你的参数的定义 @BusinessNum varchar(8000)

也改为 @BusinessNum varchar(max) 试试
[解决办法]

引用:

Quote: 引用:

另外，你的参数的定义 @BusinessNum varchar(8000)

也改为 @BusinessNum varchar(max) 试试

max 最大是多大呢

最大2G
[解决办法]
2G是容量，不是长度，超过8000的话定义多个varchar(max)的变量，然后拼接
[解决办法]

引用:

Quote: 引用:

另外，你的参数的定义 @BusinessNum varchar(8000)

也改为 @BusinessNum varchar(max) 试试

max 最大是多大呢

varchar(max)、nvarchar(max) 和 varbinary(max) 统称为大值数据类型。您可以使用大值数据类型来存储最大为 2^31-1 个字节的数据。

也就是2G字节的数据。觉得能满足你的要求了。

求解 存储过程 中 接收参数 过剩

热点推荐

求解存储过程中接收参数过剩