07_05 插入新课程的记录

来源：互联网发布：sql 2张表合并编辑：程序博客网时间：2024/05/12 06:41
CREATE PROCEDURE usp_insert_subject    @SubjectNo int OUTPUT,    @GradeId int OUTPUT,@GradeName varchar(50),@SubjectName varchar(50),    @ClassHour int = 36AS     DECLARE @errNum intSET @errNum = 0IF (LEN(RTRIM(@SubjectName))=0 OR LEN(RTRIM(@GradeName))=0)RETURN -1    BEGIN TRANSACTION    IF NOT EXISTS(SELECT * FROM Grade WHERE GradeName = @GradeName)  BEGININSERT INTO Grade (GradeName) VALUES (@GradeName)SET @errNum = @errNum + @@ERRORSELECT @GradeId=@@IDENTITY  ENDELSESELECT @GradeId=GradeId FROM Grade WHERE GradeName = @GradeNameINSERT INTO Subject (SubjectName,ClassHour,GradeId) VALUES (@SubjectName,@ClassHour,@GradeId)SET @errNum = @errNum + @@ERRORSELECT @SubjectNo=@@IDENTITYIF (@errNum > 0)  BEGINROLLBACK TRANSACTIONRETURN 0   ENDELSE  BEGINCOMMIT TRANSACTIONRETURN 1  ENDGO--调用存储过程DECLARE @SubjectNo intDECLARE @GradeId intDECLARE @GradeName varchar(50)DECLARE @SubjectName varchar(50)DECLARE @ClassHour intDECLARE @rt intSET @GradeName = 'Y2'SET @SubjectName = 'Linux'SET @ClassHour = 10EXEC @rt=usp_insert_subject @SubjectNo OUTPUT,@GradeId OUTPUT,@GradeName,@SubjectName,@ClassHourIF (@rt = 1)  BEGINPRINT '增加课程'+@SubjectName+'记录成功'PRINT '学期编号是' + CAST(@GradeId AS varchar(10)) + '，学期名称是' + @GradeNamePRINT '课程编号是' + CAST(@SubjectNo AS varchar(10)) + '，课程名称是' + @SubjectName  ENDELSE if (@rt = 0)PRINT '增加课程记录失败！'ELSEPRINT '学期名称或课程名称不能为空，请重新执行！'GO
0 0