Stored Procedures pt 2

use ReportBuilderTraining

---
--- Create a stored procedure that takes arguments and returns data
---

-- cleanup from previous runs, delete sproc if it already exists
if exists (select * from sys.procedures where name='sp_GetStudentDemographics')
	drop procedure sp_GetStudentDemographics
GO

---
--- create stored procedure
---

CREATE PROCEDURE sp_GetStudentDemographics
	 @term char(5) = 20163
	,@program int
AS
BEGIN

	select StudentId, Ethnicity, Gender
		from Student s
		where exists (

			select *
				from StudentMajor sm
				join Major m
				on sm.MajorId=m.MajorId
				where m.ProgramID = @program
				and TermId = @term
				and sm.StudentId=s.StudentId

		)

END

GO

---
--- now call stored procedure
---

exec sp_GetStudentDemographics @term='20163', @program=4

-- depend on default value for @term
exec sp_GetStudentDemographics @program=4