mysql临时表 表变量_临时表，表变量

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