暫存資料表，表變數

1.表變數
    DECLARE @indextable table
        (
        uid int identity(1,1),    
        id int
        )
2.暫存資料表
    CREATE Table #temptable (
        id int,
        date datetime        
        )

當資料量大時,建議使用暫存資料表!

放一個預存程序

@變數（包括表變數）在where 裡都變成變數，所以，在where用到表變數就得 加多一個別名，如：(select c_name from @temptab a where a.classid=T.classid )

下面是一個分頁預存程序，有空的朋友可以看看

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

create PROCEDURE [dbo].[db_blog_class_Pagination2]
(
 @strGetFields nvarchar(1500) = '*',  -- 需要返回的列
 @orderstr nvarchar(500)='1',      -- 排序的欄位名
    @joinField int=0,    -- 連結的欄位 欄位條件表首碼一定要用T 如 (select c_name from db_sys_class where nsort='consult' and classid = T.classid) as classname
 @PageSize   int = 10,          -- 頁尺寸
 @PageIndex  int = 1,           -- 頁碼
 @doCount  bit = 0,   -- 返回記錄總數, 非 0 值則返回
 @strWhere  nvarchar(1500) = ''  -- 查詢條件 (注意: 不要加 where)
)
AS
declare @strSQL   varchar(5000)     -- 主語句
declare @selectField nvarchar(2000)
set @orderstr=' c_tim desc '
set @strSQL=''
if @doCount != 0
  begin
    if @strWhere !=''
    set @strSQL = 'select count(1) as Total from db_blog_class where '+@strWhere
    else
    set @strSQL = 'select count(1) as Total from db_blog_class'
  end 
else
begin 
  if @joinField != 0
 begin
  set @selectField = @strGetFields+', (select c_name from @temptab a where a.classid=T.classid ) as ClassName '
        set @strSQL = 'DECLARE @temptab table(classid int,c_name nvarchar(250)) insert into @temptab(classid,c_name ) select classid,c_name  from db_sys_class where nsort=''blog'''
    end
  else
  set @selectField=@strGetFields

  if @PageIndex = 1
  begin
    if @strWhere != ''  
    set @strSQL =@strSQL+ ' select top ' + str(@PageSize) +' '+@selectField+'  from db_blog_class as T where ' + @strWhere + ' order by ' + @orderstr
    else
    set @strSQL =@strSQL+ ' select top ' + str(@PageSize) +' '+@selectField+'  from db_blog_class as T order by '+ @orderstr
  end
  else
  begin
         if @strwhere=''  
   begin
    SET @strSQL =@strSQL+ ' SELECT TOP ' + str(@pageSize) +' '+@selectField+' FROM'
    +'(SELECT '+@strGetFields+',ROW_NUMBER() OVER (order by '+@orderstr+') AS RowNo FROM db_blog_class) AS T '
    +'WHERE RowNo >'+str((@pageIndex-1)*@pageSize)
            end
      else IF @strWhere != ''
   begin
    SET @strSQL =@strSQL+ ' SELECT TOP ' + str(@pageSize) +' '+@selectField+' FROM'
    +'(SELECT '+@strGetFields+',ROW_NUMBER() OVER (order by '+@orderstr+') AS RowNo FROM db_blog_class WHERE ' + @strWhere + ' ) AS T '
    +'WHERE RowNo >'+str((@pageIndex-1)*@pageSize)
            end

  end
end  
exec (@strSQL)

 

 

select *,(select c_name from @temptab a where a.classid=T.classid ) from db_blog_class as T