Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- alter procedure enrollpromotions @studies nvarchar(100), @semester int
- as begin
- begin try
- begin transaction
- declare @oldIdEnrollment int = (select Enrollment.IdEnrollment from dbo.Enrollment inner join Studies on Studies.IdStudy = Enrollment.IdStudy where @studies = Studies.Name and @semester = Enrollment.Semester)
- if @oldIdEnrollment is null begin
- raiserror ('W Enrollments nie ma takiego wpisu', 16, 1)
- end
- declare @newIdEnrollment int = (select Enrollment.IdEnrollment from dbo.Enrollment inner join Studies on Studies.IdStudy = Enrollment.IdStudy where @studies = Studies.Name and @semester + 1 = Enrollment.Semester)
- if @newIdEnrollment is null begin
- select @newIdEnrollment = max(Enrollment.IdEnrollment) + 1 from dbo.Enrollment
- insert into dbo.Enrollment (IdEnrollment, Semester, IdStudy, StartDate)
- values (@newIdEnrollment, @semester + 1, (select Enrollment.IdStudy from dbo.Enrollment where Enrollment.IdEnrollment = @oldIdEnrollment), getdate())
- end
- update dbo.Student set Student.IdEnrollment = @newIdEnrollment where Student.IdEnrollment = @oldIdEnrollment
- select Enrollment.IdEnrollment, Enrollment.Semester, Studies.Name, format(Enrollment.StartDate, 'dd-MM-yyyy') as StartDate from dbo.Enrollment inner join Studies on Studies.IdStudy = Enrollment.IdStudy where Enrollment.IdEnrollment = @newIdEnrollment
- commit
- end try
- begin catch
- rollback
- declare @errorMessage nvarchar(4000)
- declare @errorSeverity int
- declare @errorState int
- select @errorMessage = error_message(), @errorSeverity = error_severity(), @errorState = error_state()
- raiserror (@errorMessage, @errorSeverity, @errorState)
- end catch
- end;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement