一、创建临时表：

1.系统

例子1：创建tempCategory，每一列定义在括号中；

if object_id('tempdb..#tempCategory') is not null    drop table #tempCategory    create table #tempCategory(        num int,        CGName varchar(50),        CGID int,        PartnerID int,        UpdTime datetime,        Operator varchar(50)    )

2.with as

例子1：创建临时表，名称：tempTable 。往临时表中填充数据；

with tempTable as    (select a.* from Category a , f_cid(@CGID) b where a.CGID = b.id  ) select CGType,count(1) as TypeCount from tempTable group by CGType

例子2：

WITH TEMPTABLE AS  ( SELECT KEYID,TITLE,D3KEY,TITLELINK,MODIFYDATE,PUBDATE,REVOKEDATE,PCIMAGEURL,ISTOP,SORTINDEX,CATEID,BPUSHINFO,ABSTRATS,ROW_NUMBER() OVER(ORDER BY SORTINDEX DESC) AS ROW  FROM INFORMATION  WHERE PUBDATE >= '2013-03-12' AND PUBDATE < '2013-03-13' AND CATEID = 1 ),   TEMPTABLE2 AS  ( SELECT * FROM TEMPTABLE WHERE ISTOP=1  UNION ALL  SELECT *  FROM TEMPTABLE WHERE GETDATE()< REVOKEDATE AND GETDATE()>PUBDATE AND ISTOP <> 1  UNION ALL  SELECT *  FROM TEMPTABLE WHERE GETDATE()> REVOKEDATE AND ISTOP <> 1)   SELECT (SELECT COUNT(1) FROM TEMPTABLE2) AS TOTALCOUNT,KEYID,TITLE,D3KEY,TITLELINK,MODIFYDATE,PUBDATE,REVOKEDATE,PCIMAGEURL,ISTOP,SORTINDEX,CATEID,BPUSHINFO,ABSTRATS,ROW  FROM TEMPTABLE2 WHERE ROW BETWEEN 1 AND 5

 说明：

　　第一部分：创建临时表TEMPTABLE ，往里面填充数据；

　　第二部分: 创建临时表TEMPTABLE2，往里面填充筛选后的TEMPTABLE 的数据；

　　　　　　  具体：TEMPTABLE2对TEMPTABLE中的数据重新排序，然后 UNION ALL 连接顺序如下：

　　　　　　　　1、Istop=1（置顶信息）；

　　　　　　　　2、 GETDATE()< REVOKEDATE AND GETDATE()>PUBDATE AND ISTOP <> 1（非置顶信息中大于发布时间而小于下线时间的信息，即今天正在向线上的信息；）

　　　　　　　　3、GETDATE()> REVOKEDATE AND ISTOP <> 1（非指定信息中当前日期大于下线时间，即已经下线的信息）

 

二、SQL分页

ALTER PROCEDURE [dbo].[CategoryList_FirstLevelList]     @CGName varchar(50),    @pagesize int,    @pageindex int,    @total int outputASBEGIN    declare @strsql varchar(3000),@strcondition varchar(200),    @subsql varchar(500),@frmindex int,@endindex int,@partnerid2 int    set @strcondition=' where CGType=1'    if(@CGName!='' and @CGName is not null)        set @strcondition=@strcondition+' and CGName like ''%'+ @CGName+'%'''    set @frmindex=(@pageindex-1)*@pagesize    set @endindex=@pageindex*@pagesize    set @subsql='select row_number() over(order by CID) as num,CGName,CGID,PartnerID,UpdTime,Operator from Category'    set @strsql='select * from ('+@subsql+@strcondition+') tb'    if object_id('tempdb..#tempCategory') is not null drop table #tempCategory    create table #tempCategory(        num int,        CGName varchar(50),        CGID int,        PartnerID int,        UpdTime datetime,        Operator varchar(50)    )            print(@strsql)    insert into #tempCategory exec(@strsql)    --往临时表中填充数据（执行SQL语句）    select @total=count(1) from #tempCategory    select CGName,CGID,c.PTID,isnull(c.partner,'') as PTname,c.[Status] as PStatus,UpdTime,Operator from #tempCategory a    left join partners c on a.PartnerID=c.PTID            where num>@frmindex and num<=@endindex    order by numEND

 三、事务

ALTER PROCEDURE [dbo].[Category_ChangeUpdTypeMult]     @CGID varchar(2000),     @Operator varchar(200),    @UpdType varchar(50),    @result int outputASBEGIN    DECLARE @SQL VARCHAR(2000)    set @result=1    begin transaction mytrans        SET @SQL = N'update Category set UpdType='''+@UpdType+''',UpdTime=GETDATE(),Operator='''+@Operator+''' where CGID in('+@CGID+')'        EXEC(@SQL)        if @@error<>0        begin                set @result=0            rollback transaction mytrans            return        end    commit transaction mytrans        END