临时表，表变量

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