1.分页：
---存储过程、row_number完成分页 if (object_id('pro_page', 'P') is not null) drop proc proc_cursor go create proc pro_page @startIndex int, @endIndex int as select count(*) from product ; select * from ( select row_number() over(order by pid) as rowId, * from product ) temp where temp.rowId between @startIndex and @endIndex go --drop proc pro_page exec pro_page 1, 4 -- --分页存储过程 if (object_id('pro_page', 'P') is not null) drop proc pro_stu go create procedure pro_stu( @pageIndex int, @pageSize int ) as declare @startRow int, @endRow int set @startRow = (@pageIndex - 1) * @pageSize +1 set @endRow = @startRow + @pageSize -1 select * from ( select *, row_number() over (order by id asc) as number from student ) t where t.number between @startRow and @endRow; exec pro_stu 2, 2;
2.删除表
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go -- <><><><><><><><><><><><><> -- <> <> -- <> 类_删除表 <> -- <> <> -- <><><><><><><><><><><><><> Create PROCEDURE [dbo].[类_删除表] @Sql_Table varchar(30) = 'temp_1' -- 要删除的数据表名 As BEGIN SET NOCOUNT ON; declare @Sql_Sql nvarchar(200) declare @uuu int set @uuu=0 set @Sql_Sql='select @uuu=count(id) from sysobjects where id = object_id(N'+''''+'[dbo].['+@Sql_Table+']'+''''+') and OBJECTPROPERTY(id, N'+''''+'IsUserTable'+''''+') = 1' EXEC sp_executesql @Sql_Sql,N'@uuu int output',@uuu OUTPUT if @uuu>0 exec('drop table '+@Sql_Table) END
转载于:https://www.cnblogs.com/tzq9308/p/4303685.html
