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

Question

如题 ，我的存储过程 定义 的 参数 是 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),
      @TotalNum varchar(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 

Answer 1

把 DECLARE @sql VARCHAR(8000) 
       改为： DECLARE @sql VARCHAR(max) 试试 

Answer 2

你的问题在于，由于你的拼接语句中包含的字符太多，超过了你上面定义的varchar(8000),所以才报错的

Answer 3

另外，你的参数的定义 @BusinessNum varchar(8000)也改为  @BusinessNum varchar(max) 试试

Answer 4

恩 ，我 知道 这个问题 ，但是 如何 解决呢，我 的 businessNum 很有可能 比 8000 还多 

Answer 5

max 最大 是 多大 呢

Answer 6

max 最大 是 多大 呢最大2G

Answer 7

2G是容量，不是长度，超过8000的话定义多个varchar(max)的变量，然后拼接

Answer 8

max 最大 是 多大 呢varchar(max)、nvarchar(max) 和 varbinary(max) 统称为大值数据类型。您可以使用大值数据类型来存储最大为 2^31-1 个字节的数据。也就是2G字节的数据。觉得能满足你的要求了。

Answer 9

你可以看看微软的文档：http://technet.microsoft.com/zh-cn/library/ms176089.aspx

Answer 10

可以看看邹老大的博客http://blog.csdn.net/zjcxc/article/details/20075

Answer 11

--#1.这样拼接,容易SQL注入,建议用参数化查询:先拼接好可供参数化查询的SQL,再调用sp_executesql存储过程来执行.
--#2.当然,#1不是你的例子中讨论的重点. 针对你的问题,只要把VARCHAR(8000)换成VARCHAR(MAX),且在拼接SQL时,注意如何让两个字符串相加的结果还是VARCHAR(MAX)类型即可
ALTER PROCEDURE [dbo].[QDZYExportCostExcel_EX]
@BusinessNum VARCHAR(MAX), --这儿如果长度不够的话,可以改成varchar(max),个人建议你用XML类型传参,再在存储过程中解析调用
@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),
@TotalNum VARCHAR(12),
@DocumentNum VARCHAR(50),
@ContainerNum VARCHAR(50),
@Flag_Customs VARCHAR(1),
@EndInputDate DATETIME,
@StartInputDate DATETIME
AS
BEGIN 
        DECLARE @sql VARCHAR(max) --这儿就一定要定义成varchar(max)类型了. 楼主考虑是否该用nvarchar(max)类型
--给@sql变量赋值时,一定要把字符串转换成varchar(max)类型
        SET @sql = CAST('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'' '  AS VARCHAR(MAX))
--下面这些逻辑,不用变,因为varchar(max) + varchar(8000) 还是等于varchar(max) (以前的逻辑相当于varchar(8000) + varchar(8000) 等于varchar(8000),所以@sql长度始终超不过8000)
            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 ) --看一下这回应该正确了
select len(@sql) --测试一下长度是否已经超过8000  
            --EXEC(@sql)  
END GO--测试一下结果
declare @BusinessNum varchar(max)
set @BusinessNum = cast('a' as varchar(max)) + replicate('b', 8000) + 'c' --长为8002exec [dbo].[QDZYExportCostExcel_EX]
@BusinessNum = @BusinessNum,
@Consignor = null,
@StartExportDate = null,
@EndExportDate  = null,
@ExportMan  = null,
@ExportType  = null,
@InputMan  = null,
@InputDepartment  = null,
@StartManagerCheckCostDate  = null,
@EndManagerCheckCostDate  = null,
@Flag_ManagerCheckCost  = null,
@DeliveryListNum  = null,
@AdvanceNum = null,
@ApproveNum  = null,
@VoyageNum  = null,
@TotalNum  = null,
@DocumentNum  = null,
@ContainerNum  = null,
@Flag_Customs  = null,
@EndInputDate  = null,
@StartInputDate  = NULL--很明显,已经突破8000的限制
/*
(无列名)
17736
*/