[故纸堆三]存储过程技术

1，按条件查询并分页排序结果 

-- sp_GetLeaveWordPartBySql  分页查找班级留言(按时间排序)
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetLeaveWordPartBySql]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetLeaveWordPartBySql]
GO

CREATE PROCEDURE sp_GetLeaveWordPartBySql
@ParentID  UniqueIDentifier,
@ContentLength int,
@PageSize  int,
@BeginRow  int,
@Ask   NVarChar(128)

as
DECLARE @sql  NVarChar(1024)
DECLARE @select NVarChar(256)
DECLARE @from  NVarChar(256)
DECLARE @where  NVarChar(256)
DECLARE @order  NVarChar(64)
DECLARE @BeginDate DateTime

BEGIN
     
SELECT @select = 'SELECT  LeaveWord.* ,UserName,isnull(SmallIcon,''../photo/s/DEF_ICON.gif'') as SmallIcon ';
SELECT @from = ' FROM LeaveWord INNER JOIN MemberInfo ON LeaveWord.UserID = MemberInfo.UserID LEFT OUTER JOIN Icon ON MemberInfo.IconID = Icon.IconID ';
SELECT @where = ' WHERE  '+@Ask;
SELECT @order = ' ORDER BY PublishDate DESC  '; 

 

 IF  @BeginRow > 1
  BEGIN
  
  SET ROWCOUNT @BeginRow
  
  IF @ParentID IS NOT NULL
  BEGIN
   Declare Cur_trans Cursor local DYNAMIC
   FOR SELECT  PublishDate From LeaveWord Where ParentID = @ParentID ORDER BY PublishDate DESC
  END
  ElSE
  BEGIN
   Declare Cur_trans Cursor local DYNAMIC
   FOR SELECT  PublishDate From LeaveWord ORDER BY PublishDate DESC
  END
 
  Open Cur_trans

  FETCH NEXT FROM Cur_trans Into @BeginDate
  WHILE @@FETCH_STATUS = 0
  BEGIN
     FETCH NEXT FROM Cur_trans Into @BeginDate
  END
  Close Cur_trans
  Deallocate Cur_trans
  END
 ELSE
  SELECT @BeginDate = NULL
 BEGIN

 SET ROWCOUNT @PageSize
 
 IF @ContentLength <> -1
 BEGIN
  SELECT @select = ' SELECT  LeaveWordID, ParentID, UserID, Title, LEFT(CAST(Content AS NvarChar(1024)),'+CAST(@ContentLength as VarChar(4)) +') + ''''...'''' AS Content,PublishDate ,UserName,isnull(SmallIcon,''../photo/s/DEF_ICON.gif'') as SmallIcon ';
 END

 IF @ParentID IS NOT NULL
 BEGIN
  SELECT @where = @where + ' and ParentID = ''' + Cast(@ParentID AS Nvarchar(36))+ '''';
 END
 
 
 IF @BeginDate IS NOT NULL
 BEGIN
  SELECT @where = @where + ' and PublishDate < '''+ Convert(Varchar(24),@BeginDate ,21 ) +'''';
 END

 SELECT @sql = @select + @from + @where + @order;

 Exec sp_executesql @sql;

 END
END

GO
2，临时表、union，加入自增序号等

-- sp_GetAllUseCarByDepAndTime  查找所有车辆使用信息
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetAllUseCarByDepAndTime]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetAllUseCarByDepAndTime]
GO

CREATE PROCEDURE sp_GetAllUseCarByDepAndTime
@DepID int,
@begin  DateTime,
@end  DateTime

AS

SELECT IDENTITY(INT, 1, 1) AS WDIndexID, CarUseInfo.DeptID,CarUseInfo.CarUseID, CarUseInfo.UseUID,CarUseInfo.OtherFee,
 tbUserInfo.nvchUserName as UsenvchUserName, CarUseInfo.Cost,CarUseInfo.Distance, ( isnull(tbDeptInfo.nvchDeptName,'') + isnull(SubDeptInfo.SubDeptName,''))  as nvchDeptName,
 CarInfo.CarLicense,CarInfo.PricePerKM , CASE CarUseFlag When 1 Then '未审核' When 2 Then '通过' Else '未通过' END as CheckType,
      CarUseInfo.BeginTime, CarUseInfo.EndTime, CASE IsOut WHEN  1 Then '是' Else  '否' End AS IsOutT,
      CarUseInfo.TargetID, TargetInfo.TargetName, CarUseInfo.OrderCarID,
      CarUseInfo.CarID, CarUseInfo.UseCarType,TypeCode.TypeName,
 CarUseInfo.Fee,CarUseInfo.Distance * CarUseInfo.Fee as SumFee,PassengerAmount,StartPlace, ChauffeurInfo.ChauffeurName
INTO #tmp

FROM CarUseInfo LEFT OUTER JOIN  tbDeptInfo ON
      tbDeptInfo.iDeptId = CarUseInfo.DeptID
       LEFT OUTER JOIN  tbUserInfo ON
       tbUserInfo.iUserID = CarUseInfo.UseUID
        LEFT OUTER JOIN  CarInfo ON
       CarInfo.CarID = CarUseInfo.CarID
        LEFT OUTER JOIN  TargetInfo ON
       TargetInfo.TargetID = CarUseInfo.TargetID
 LEFT OUTER JOIN  TypeCode ON
 (CarUseInfo.UseCarType = TypeCode.TypeValue) AND TypeCode.TypeItem = 'CarUseType'
 LEFT JOIN SubDeptInfo ON
 SubDeptInfo.SubDeptID  = CarUseInfo.SubDeptID
        LEFT OUTER JOIN  ChauffeurInfo ON
       ChauffeurInfo.ChauffeurID = CarUseInfo.ChauffeurID
WHERE (CarUseInfo.DeptID = @DepID) AND (BeginTime BETWEEN @begin AND @end)

ORder By BeginTime Desc

 

(SELECT * FROM #tmp) union
(
 SELECT max(WDIndexId) + 1, null,null, null,sum(OtherFee ) ,
 null, sum(Cost),sum(Distance), '合计'  as nvchDeptName,
 null,null , null,
      null, null,null,
      null, str(count(*)), null,
      null,null ,null,
 null,sum(Distance *Fee) as SumFee,sum(PassengerAmount) ,'次数', null
From #tmp

) order by WDIndexId

DROP Table #tmp

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO