存储过程/使用while和游标

                                                存储过程/使用while和游标

先建立一个表：

CREATE TABLE Studentgrade
(id INT IDENTITY(1,1), --学生ID或者用学号
 name VARCHAR(40), --姓名
 grade INT--成绩
 )
 
 SELECT * FROM Studentgrade
 
 INSERT Studentgrade
 VALUES('东郭先生',30)
 INSERT Studentgrade
 VALUES('南郭先生',60)
 INSERT Studentgrade
 VALUES('诸葛亮',70)
 INSERT Studentgrade
 VALUES('张飞',10)
 INSERT Studentgrade
 VALUES('刘备',90)

 

 

-- Author:  rorger
-- Create date: 2010-10-18 20:13:10
-- Description:   统计不及格学生数

-- =============================================
CREATE PROCEDURE CountGradeNotPass
 -- Add the parameters for the stored procedure here
 @number INT OUTPUT
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here
     DECLARE @id int
     DECLARE @name VARCHAR(40)
     DECLARE @grade INT
     SET @number=0
   DECLARE cursor1 CURSOR FOR
  SELECT * FROM Studentgrade
  OPEN cursor1
  FETCH NEXT FROM cursor1 INTO @id,@name,@grade
  WHILE  @@FETCH_STATUS=0
  BEGIN
     IF @grade <60
         SET @number=@number+1
         FETCH NEXT FROM cursor1 INTO @id,@name,@grade
  END
 
  CLOSE cursor1
    DEALLOCATE cursor1
  END
GO

DECLARE @number INT
EXEC CountGradeNotPass @number OUTPUT
PRINT '不及格人数为：'+STR(@number)+' 个'