create procedure procname
AS
declare @recount int
select @recount=count(id) from school
--最关键在这句
select @recount=count(id) from school
select @recount=select count(id) from school -- 错误:这里就不再需要select了
set @recount=count(id) from school -- 错误这里只能用select不能用set
如果school为一个@chart参数的话,上面方法就不适用了
应该为:
declare
@getRecordSql
nvarchar
(
100
)
set @getRecordSql = ' select @recordcount=count(1) from ' + @chart
exec sp_executesql @getRecordSql ,N ' @recordCount int output ' , @recordCount output
set @getRecordSql = ' select @recordcount=count(1) from ' + @chart
exec sp_executesql @getRecordSql ,N ' @recordCount int output ' , @recordCount output
N'@recordCount int output',@recordCount output
做一个参数的替换,如果测试一下:print @recordCount的话就会显示行数
//示例:选择最新的5条数据从20条开始
execute
A_School_getNew
5
,
20
-- 存储过程
create procedure A_School_getNew
@Size int ,
@StartIndex int
AS
declare @recount int
declare @Sql nvarchar ( 120 )
select @recount = count (id) from school
set @Sql = ' select top ' + convert ( nvarchar , @Size ) + ' id,school_name from school where( ' +
' ID not in(select top ' + convert ( nvarchar , @recount - @startIndex + @size ) +
' id from school)) order by id desc ' ;
execute ( @Sql )
-- 存储过程
create procedure A_School_getNew
@Size int ,
@StartIndex int
AS
declare @recount int
declare @Sql nvarchar ( 120 )
select @recount = count (id) from school
set @Sql = ' select top ' + convert ( nvarchar , @Size ) + ' id,school_name from school where( ' +
' ID not in(select top ' + convert ( nvarchar , @recount - @startIndex + @size ) +
' id from school)) order by id desc ' ;
execute ( @Sql )