Advertisement
Guest User

Untitled

a guest
Apr 7th, 2020
242
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.80 KB | None | 0 0
  1. alter procedure enrollpromotions @studies nvarchar(100), @semester int
  2. as begin
  3.     begin try
  4.         begin transaction
  5.         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)
  6.         if @oldIdEnrollment is null begin
  7.              raiserror ('W Enrollments nie ma takiego wpisu', 16, 1)
  8.         end
  9.         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)
  10.         if @newIdEnrollment is null begin
  11.             select @newIdEnrollment = max(Enrollment.IdEnrollment) + 1 from dbo.Enrollment
  12.             insert into dbo.Enrollment (IdEnrollment, Semester, IdStudy, StartDate)
  13.             values (@newIdEnrollment, @semester + 1, (select Enrollment.IdStudy from dbo.Enrollment where Enrollment.IdEnrollment = @oldIdEnrollment), getdate())
  14.         end
  15.         update dbo.Student set Student.IdEnrollment = @newIdEnrollment where Student.IdEnrollment = @oldIdEnrollment
  16.         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
  17.         commit
  18.     end try
  19.     begin catch
  20.         rollback
  21.         declare @errorMessage nvarchar(4000)
  22.         declare @errorSeverity int
  23.         declare @errorState int
  24.         select @errorMessage = error_message(), @errorSeverity = error_severity(), @errorState = error_state()
  25.         raiserror (@errorMessage, @errorSeverity, @errorState)
  26.     end catch
  27. end;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement