Stored Procedures pt 3

use ReportBuilderTraining

---
--- Create a stored procedure that takes arguments and returns data.
---
--- designed to take a multi-valued parameter from SSRS for @term
---

-- 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 PROCEDURE sp_GetStudentDemographics
	 @term nvarchar(4000)
	,@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 in (select value from fnSplitString(@term, ','))
				and sm.StudentId=s.StudentId

		)

END

go

exec sp_GetStudentDemographics @term=N'20173, 20175', @program=1