分享Sql Server 存储过程使用方法

create procedure getGroup(@salary int)
as
begin
? ?SELECT d_id AS '部门编号', AVG(e_salary) AS '部门平均工资' FROM employee
　　GROUP BY d_id?
　　HAVING AVG(e_salary) > @salary
end ? ??

select top 10 * from table ?--查询表前10条数据?
select top 10 * from table where id not in (select top (10) id ?from tb) --查询前10条数据 ?（条件是id 不属于table 前10的数据中）

select top 10 * from table where id not in (select top ((3-1) * 10) id ?from tb) --查询前10条数据 ?（条件是id 不属于table 前10的数据中）

create proc sp_pager
(
? ? @size int , --每页大小
? ? @index int --当前页码
)
as
begin
? ? declare @sql nvarchar(1000)
? ? if(@index = 1)?
? ? ? ? set @sql = 'select top ' + cast(@size as nvarchar(20)) + ' * from tb'
? ? else?
? ? ? ? set @sql = 'select top ' + cast(@size as nvarchar(20)) + ' * from tb where id not in( select top '+cast((@index-1)*@size as nvarchar(50))+' id ?from tb )'
? ? execute(@sql)
end

create proc sp_pager
(
? ? @size int,
? ? @index int
)
as
begin
? ? select * from ( select row_number() over(order by id ) as [rowId], * from table) as b
? ? where [rowId] between @size*(@index-1)+1 ?and @size*@index
end