Stored Procedures

use ReportBuilderTraining

---
--- rights a stored procedure designed to be run nightly that updates each student's current GPA
--- based on data in the StudentHistory table
---
--- experminent with creating/alterings stored procedures, try/catch error handling and transactions
---
--- In truth, the entire updated could be written in one statement, but we handle the task less effeciently here
--- to demonstrate the concepts.
---


-- add a CurrentGPA field to the Student table, if is doesn't already exist

if not exists (select * from information_schema.columns where TABLE_SCHEMA='dbo' and TABLE_NAME='Student' and COLUMN_NAME='CurrentGPA')
	alter table dbo.Student add CurrentGPA numeric(4,2) null

go


---
--- Create the procedure
---

CREATE PROCEDURE dbo.sp_UpdateCurrentGPA
AS
BEGIN

	--
	-- get most recent GPA for each student and write to temp table
	-- 
	-- note that we don't need to worry about #temp already existing
	--
	select 
		 StudentId
		,CumulativeGPA
		,row_number() over (partition by StudentId order by TermID desc) row
		into #temp
		from dbo.StudentHistory
		where CumulativeGPA is not NULL

	delete from #temp where row!=1

	
	BEGIN TRANSACTION

	BEGIN TRY

		update dbo.Student set CurrentGPA = NULL

		update dbo.Student
			set CurrentGPA=t.CumulativeGPA
			from dbo.Student s
			join #temp t
			on s.StudentId=t.StudentId

		COMMIT TRANSACTION

	END TRY
	BEGIN CATCH

		ROLLBACK TRANSACTION

	END CATCH

END


GO


select * from dbo.Student
go

exec  dbo.sp_UpdateCurrentGPA
go

select * from dbo.Student
go





---
--- Now, edit stored procedure to intentionally cause and error, and see error handling work
---

ALTER PROCEDURE dbo.sp_UpdateCurrentGPA
AS
BEGIN

	--
	-- get most recent GPA for each student
	--
	select 
		 StudentId
		,CumulativeGPA
		,row_number() over (partition by StudentId order by TermID desc) row
		into #temp
		from dbo.StudentHistory
		where CumulativeGPA is not NULL

	delete from #temp where row!=1

	BEGIN TRANSACTION

	BEGIN TRY

		update dbo.Student set CurrentGPA = NULL

		update dbo.Student set CurrentGPA = 1./0

		update dbo.Student
			set CurrentGPA=t.CumulativeGPA
			from dbo.Student s
			join #temp t
			on s.StudentId=t.StudentId

		COMMIT TRANSACTION

	END TRY
	BEGIN CATCH

		print ERROR_MESSAGE()

		--
		-- should really test transaction state here with xact_state()
		-- and should also worry about "nested" transactions (hint: they are not nested at
		-- all, a rollback rolls back all the way to the first "begin transaction")
		--
		-- See https://docs.microsoft.com/en-us/sql/t-sql/language-elements/try-catch-transact-sql
		--

		ROLLBACK TRANSACTION

	END CATCH

END


GO


exec  dbo.sp_UpdateCurrentGPA
go

select * from dbo.Student
go