create proc ZZ_Relation
(

@pagesize     int = 10,         ----每页显示的记录个数
@pageindex         int = 1         ----要显示那一页的记录

)
AS



create table #Tmp --创建临时表#Tmp
(
    ID    int IDENTITY (1,1)      not null, --创建列ID,并且每次新增一条记录就会加1
    SPDM                 varchar(200),  
SPMC     varchar(200),
CA_NAME    varchar(200),
GG1DM varchar(200),
GG2DM varchar(200),
BZSJ     varchar(200)
    primary key (ID)       --定义ID为临时表#Tmp的主键      
)


insert into #Tmp
select SHANGPIN.SPDM,SHANGPIN.SPMC,COMMODITY_AUTO.CA_NAME ,GG1DM,GG2DM,BZSJ from
SHANGPIN LEFT JOIN COMMODITY_AUTO ON SHANGPIN.SPDM=COMMODITY_AUTO.CA_CODE left join ProductUpDown on ProductUpDown.SPDM=COMMODITY_AUTO.CA_CODE
where ProductUpDown.ISUP=1


DECLARE @sql VARCHAR(1000)

if(@pageindex=1)
begin
set @sql='select top '+ CAST(@pagesize as varchar(10)) + '* from #Tmp'
end
else
begin
set @sql='select top '+CAST(@pagesize as varchar(10))+' * from #Tmp where id not in( select top '+ CAST(@pagesize*(@pageindex-1) as varchar(10)) +' id from #Tmp )'
end

exec(@sql)

drop table #Tmp    --删除临时表#Tmp



今天写这个存储过程费劲死了,好歹终于完成了,有一种让人虚脱掉的感觉


Leave a Reply