SQL碎片-表值函数

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

--表值函数,返回房屋类型统计复表
ALTER function [dbo].[HL_fTotal]()
  returns @TotalTable table
(
 ItemTotal int,
 AreaTotal float,
 BuildingTotal int,
 SetsTotal int,
 SetsCanSell int,
 AreaCanSell float
)
as
begin
 declare @tempItemTotal int, @tempAreaTotal float, @tempBuildingTotal int, @tempSetsTotal int, @tempSetsCanSell int, @tempAreaCanSell int;
 --取得项目总数
    select @tempItemTotal = count(*) from AC_ItemTotal
    --取得总面积
  declare @tempfloat1 float
  set @tempfloat1 = 0.00
  declare AreaTotal_cursor cursor
   for select AreaTotal from AC_ItemTotal
  open AreaTotal_cursor
  fetch next from AreaTotal_cursor into @tempAreaTotal
  while @@FETCH_STATUS = 0
   begin
    set @tempfloat1 = @tempAreaTotal + @tempfloat1
    fetch next from AreaTotal_cursor into @tempAreaTotal
   end
  close AreaTotal_cursor
 --取得总幢数
 select @tempBuildingTotal = count(*) from NH_BuildingInfo
 --取得总套数
  declare @tempfloat2 int
  set @tempfloat2 = 0
  declare SetsTotal_cursor cursor
   for select SetsTotal from AC_ItemTotal
  open SetsTotal_cursor
  fetch next from SetsTotal_cursor into @tempSetsTotal
  while @@FETCH_STATUS = 0
   begin
    set @tempfloat2 = @tempSetsTotal + @tempfloat2
    fetch next from SetsTotal_cursor into @tempSetsTotal
   end
  close SetsTotal_cursor
 --取得可售套数
  declare @tempfloat3 float
  set @tempfloat3 = 0.00
  declare SetsCanSell_cursor cursor
   for select SetsCanSell from AC_ItemTotal
  open SetsCanSell_cursor
  fetch next from SetsCanSell_cursor into @tempSetsCanSell
  while @@FETCH_STATUS = 0
   begin
    set @tempfloat3 = @tempSetsCanSell + @tempfloat3
    fetch next from SetsCanSell_cursor into @tempSetsCanSell
   end
  close SetsCanSell_cursor
 --可售面积
  declare @tempfloat4 int
  set @tempfloat4 = 0
  declare AreaCanSell_cursor cursor
   for select AreaCanSell from AC_ItemTotal
  open AreaCanSell_cursor
  fetch next from AreaCanSell_cursor into @tempAreaCanSell
  while @@FETCH_STATUS = 0
   begin
    set @tempfloat4 = @tempAreaCanSell + @tempfloat4
    fetch next from AreaCanSell_cursor into @tempAreaCanSell
   end
  close AreaCanSell_cursor

  insert @TotalTable values(@tempItemTotal,@tempfloat1,@tempBuildingTotal,@tempfloat2,@tempfloat3,@tempfloat4)
  return
end

----------------------------------------------------调用--------------------------------------------
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[UP_NH_IndexTotal_GetModel]
 AS
 SELECT * FROM dbo.HL_fTotal()

---------------------------------------------表现层片断----------------------------------------
            ///<summary>
            ///综合统计
            ///</summary>
            DataTable dtIndexTotal = new DataTable();
            dtIndexTotal = dtCacheCheck("deCheIndexTotal","UP_NH_IndexTotal_GetModel","Day",null);
            if(dtIndexTotal.Rows.Count == 1)
            {
                lbTotal.Text = "入网销售项目总数: " + dtIndexTotal.Rows[0][0].ToString() + " 入网销售总面积: " + dtIndexTotal.Rows[0][1].ToString() + " M2 总幢数:  " + dtIndexTotal.Rows[0][2].ToString() + "  总套数:  " + dtIndexTotal.Rows[0][3].ToString() + "  可售套数:  " + dtIndexTotal.Rows[0][4].ToString() + "  可售面积:  " + dtIndexTotal.Rows[0][5].ToString() + " M2...";
            }