求解 存储过程 中 接收参数 过多
如题 ，我的存储过程 定义 的 参数 是 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
