sql存储过程

exec sp_helptext SVB_ListLoopBOut
CREATE PROCEDURE SVB_ListLoopBOut
@CompanyName VarChar(64),
@DeliverSN VarChar(32),
@GBOutHID VarChar(32),
@Status Int,
@TankCode VarChar(32),
@ICompanyName VarChar(128),
@IsUpdate Int,
@PageIndex Int,
@PageSize Int,
@Begin datetime,
@End datetime,
@Count Int output
AS
Set @CompanyName=replace(@CompanyName,'''','''''')
Set @DeliverSN=replace(@DeliverSN,'''','''''')
Set @GBOutHID=replace(@GBOutHID,'''','''''')
Set @TankCode=replace(@TankCode,'''','''''')
Set @ICompanyName=replace(@ICompanyName,'''','''''')
declare @select varchar(1000) --查询字段
declare @Tablename varchar(500)-- 查询表包含连接字符串
declare @filed varchar(100) --排序字段
declare @order int -- 0 asc 1：desc
declare @strWhere varchar(2000) --查询条件
SET @select='SVB.zLoopHID,SVB.zStatus,BC.zNameCHN,zICompanyName,zOrigReqOutID,'
SET @select=@select+' CASE WHEN SVB.zBookingDate = ''1900-01-01'' THEN '''' ELSE CONVERT(VARCHAR(16),SVB.zBookingDate,120) END AS zBookingDate,'
SET @select=@select+' CASE WHEN SVB.zSVBExpired = ''1900-01-01'' THEN '''' ELSE CONVERT(VARCHAR(16),SVB.zSVBExpired,120) END AS zSVBExpired,'
SET @select=@select+' SVB.zDeliverSN,zIssueDate,'
SET @select=@select+' SVB.zDateUpdated,SVB.zHandledBy,SVB.zCheckedBy,sum(zAmount)as zAmount '
--(select sum(zAmount) from zSVBgoodTruckOUTDetail where zGBOutHID=SVB.zGBOutHID)as zAmount,
--(select sum(isnull(zAmtOut,0)) from zTmogoodOut where zJoBID=SVB.zGBOutHID )as zFactOutAmt '

set @Tablename='zSvbGoodTruckOutLoopHead SVB '
set @Tablename=@Tablename+' inner join zSvbGoodTruckOutLoopDetail SD on SVB.zLoopHID=SD.zLoopHID '
set @Tablename=@Tablename+' inner join zCrsreqOutDetail CD on CD.zReqOutDID=SD.zReqOutDID '
set @Tablename=@Tablename+' inner join zCrsreqOutHead CH on CH.zReqOutHID=CD.zReqOutHID '
-- SET @Tablename=@Tablename+' inner join zFamTankInfo FT on FT.zTankID=SVB.zTankID '
SET @Tablename=@Tablename+' INNER JOIN zBasCompanyInfo BC on BC.zCompanyID =SVB.zCompanyID'
SET @strWhere=' 1=1 '
if(@CompanyName!='')
begin
SET @strWhere=@strWhere+' AND (BC.zNameCHN LIKE ''%'+@CompanyName+'%'' or BC.zShortName like ''%'+@CompanyName+'%'')'
end
if(@DeliverSN!='')
begin
SET @strWhere=@strWhere+' AND SVB.zDeliverSN LIKE ''%'+@DeliverSN+'%'' '
end
if(@GBOutHID!='')
begin
SET @strWhere=@strWhere+' AND SVB.zGBOUTHID LIKE ''%'+@GBOutHID+'%'' '
end

if(@ICompanyName!='')
begin
SET @strWhere=@strWhere+' AND ZICompanyName LIKE ''%'+@ICompanyName+'%'' '
end
SET @strWhere=@strWhere+' AND zIssueDate between '''+convert(varchar,@Begin,20)+''' and '''+convert(varchar,@End,20)+ ''''
SET @strWhere=@strWhere+' AND SVB.zStatus=(case when '+CAST(isnull(@Status,-1) AS VARCHAR)+'<0 then SVB.zStatus when '+CAST(@Status AS VARCHAR)+' between 0 and 2 then '+CAST(@Status AS VARCHAR)+' else SVB.zStatus end)'
SET @strWhere=@strWhere+' group by SVB.zLoopHID,SVB.zStatus,BC.zNameCHN,zICompanyName,zOrigReqOutID,SVB.zBookingDate,zSVBExpired,SVB.zDeliverSN,zIssueDate,SVB.zDateUpdated,SVB.zHandledBy,SVB.zCheckedBy '

set @order=1
set @filed=' SVB.zDateUpdated '
exec pro_fenye @select,@Tablename
,@filed,@order,@IsUpdate,@strWhere,@PageSize,@PageIndex,@Count output