• 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

  • 相关阅读:
    atitit.解决struts2 SpringObjectFactory.getClassInstance NullPointerException
    atitit。 hb Hibernate sql 查询使用
    atitit.插件体系设计总结o73.doc
    Atitit.可视化编程jbpm6 的环境and 使用总结...
    paip.自动import的实现跟java.lang.SecurityException Prohibited package name java
    Linux下tar.xz结尾的文件的解压方法
    ubuntu下的Samba配置:使每个用户可以用自己的用户名和密码登录自己的home目录
    ubuntu Linux离线安装软件包
    C语言文件操作解析(一)[转载]
    PDF XChange Viewer文件关联
  • 原文地址:https://www.cnblogs.com/zhudezhiwansui/p/8568554.html
Copyright © 2020-2023  润新知