SQL存储过程学习笔记

一、创建临时表：

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 output
AS
BEGIN
    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 num
END

 三、事务

ALTER PROCEDURE [dbo].[Category_ChangeUpdTypeMult] 
    @CGID varchar(2000), 
    @Operator varchar(200),
    @UpdType varchar(50),
    @result int output
AS
BEGIN
    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