--分页显示存储过程
if(exists(select * from sys.objects where name='pr_ShowPage'))
drop proc pr_ShowPage
go
create proc pr_ShowPage
(
@where nvarchar(500),
@pageIndex int,
@pageSize int,
@totalCount int output,
@pageCount int output
)
as
begin
declare @strcount nvarchar(500)=''
set @strcount='select @totalCount=count(*) from
(select ROW_NUMBER() over(order by UId) RowNumber,* from UserInfo
where 1=1 '+@where+') t'
exec sp_executesql @strcount,N'@totalCount int output',@totalCount output
set @pageCount=CEILING(@totalCount*1.0/@pageSize)
declare @strsql nvarchar(500)=''
declare @startRow int,@endRow int
set @startRow=(@pageIndex-1)*@pageSize+1
set @endRow=@pageIndex*@pageSize
set @strsql='select * from
(select ROW_NUMBER() over(order by UId) RowNumber,* from UserInfo where 1=1 '+@where+') t
where t.RowNumber between '+CAST(@startRow as nvarchar(5))+' and '+CAST(@endRow as nvarchar(5))+''
exec(@strsql)
end
declare @count int,@page int
exec pr_ShowPage '',2,5,@count output,@page output
select @count,@page
--添加存储过程
if (exists(select *from sys.objects where name ='Proc_Add'))
drop proc Proc_Add
go
create proc Proc_Add
(
@ID int,
@name varchar(50),
@UPhosne varchar(20),
@Bis bit,
@Upnum float
)
as
begin
begin tran
begin try
-- 添加语句
insert into UserInfo values (@ID,@name,@UPhosne,@Bis,@Upnum)
commit tran
end try
begin catch
print error_message()
rollback tran
end catch
end