07_05 插入新课程的记录

CREATE PROCEDURE usp_insert_subject
    @SubjectNo int OUTPUT,
    @GradeId int OUTPUT,
	@GradeName varchar(50),
	@SubjectName varchar(50),
    @ClassHour int = 36
AS 
    DECLARE @errNum int
	SET @errNum = 0
	
	IF (LEN(RTRIM(@SubjectName))=0 OR LEN(RTRIM(@GradeName))=0)
		RETURN -1

    BEGIN TRANSACTION
    IF NOT EXISTS(SELECT * FROM Grade WHERE GradeName = @GradeName)
	  BEGIN
		INSERT INTO Grade (GradeName) VALUES (@GradeName)
		SET @errNum = @errNum + @@ERROR
		SELECT @GradeId=@@IDENTITY
	  END
	ELSE
		SELECT @GradeId=GradeId FROM Grade WHERE GradeName = @GradeName

	INSERT INTO Subject (SubjectName,ClassHour,GradeId) 
		VALUES (@SubjectName,@ClassHour,@GradeId)
	SET @errNum = @errNum + @@ERROR

	SELECT @SubjectNo=@@IDENTITY

	IF (@errNum > 0)
	  BEGIN
		ROLLBACK TRANSACTION
		RETURN 0 
	  END
	ELSE
	  BEGIN
		COMMIT TRANSACTION
		RETURN 1
	  END
GO

--调用存储过程
DECLARE @SubjectNo int
DECLARE @GradeId int
DECLARE @GradeName varchar(50)
DECLARE @SubjectName varchar(50)
DECLARE @ClassHour int
DECLARE @rt int

SET @GradeName = 'Y2'
SET @SubjectName = 'Linux'
SET @ClassHour = 10
EXEC @rt=usp_insert_subject @SubjectNo OUTPUT,@GradeId OUTPUT,@GradeName,@SubjectName,@ClassHour
IF (@rt = 1)
  BEGIN
	PRINT '增加课程'+@SubjectName+'记录成功'
	PRINT '学期编号是' + CAST(@GradeId AS varchar(10)) + '，学期名称是' + @GradeName
	PRINT '课程编号是' + CAST(@SubjectNo AS varchar(10)) + '，课程名称是' + @SubjectName
  END
ELSE if (@rt = 0)
	PRINT '增加课程记录失败！'
ELSE
	PRINT '学期名称或课程名称不能为空，请重新执行！'
GO