存储过程中将sql语句结果赋值给变量

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

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 )

 

转载于:https://www.cnblogs.com/newmin/archive/2009/06/22/1508314.html