数据库05 存储过程

带参数的简单存储过程

use DB_TeachingMS
go
create procedure CourseClassQuery02 @CourseClassID char(10)
as 
select TS.StuID,StuName,CommonScore,MiddleScore,LastScore,TotalScore
from TB_Student TS,TB_Grade TG 
where TS.StuID=TG.StuID and CourseClassID=@CourseClassID

#执行
exec CourseClassQuery02 'T080040401'

USE DB_TeachingMS
GO
CREATE PROCEDURE AutoGetStuID @ClassID CHAR(6),@NewStuID CHAR(8) 
OUTPUT 
AS
--定义变量--
DECLARE @MaxStuID CHAR(8),@CharTwoStuID CHAR(2),@IntTwoStuID INT
  --获取班级中已有学生中最大学号--
SET @MaxStuID = (SELECT MAX(StuID) FROM TB_Student 
WHERE ClassID=@ClassID)
    --SELECT @MaxStuID
    IF @MaxStuID IS NULL
         SET @NewStuID = @ClassID+'01'
   ELSE
         BEGIN
              --从最大学号中获取班级编码和最后两位流水号--
              SET @CharTwoStuId = SUBSTRING(@MaxStuID,7,2)
              --SELECT @CharTwoStuID
              --最后两位流水号转换为数值型，然后加1，再转换成字符型--
              SET @IntTwoStuID = CONVERT(INT,@CharTwoStuID)+1
              --SELECT @IntTwoStuID
              SET @CharTwoStuID = CONVERT(CHAR,@IntTwoStuID)
              --SELECT @CharTwoStuID
              --如果转换成字符型的流水号是位，则在它前面添加字符'0'--
              IF LEN(@CharTwoStuID) = 1
                  SET @CharTwoStuID='0'+@CharTwoStuID
              --将班级编码与获取的新流水号相连接，得到新学号--
              SET @NewStuID = @ClassID+@CharTwoStuID
        END

DECLARE @GetedStuID CHAR(8)
EXEC AutoGetStuID '040801',@GetedStuID OUTPUT
SELECT @GetedStuID AS NewStuID