Untitled
unknown
plain_text
2 years ago
4.5 kB
8
Indexable
USE [DaymapBranch] GO /****** Object: StoredProcedure [dbo].[pr_FindStudents] Script Date: 6/21/2023 1:26:03 AM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO /* Added: "OR s.Firstname + ' ' + s.Surname LIKE '%' + @Name + '%')" in the WHERE clause. This adds the firstname and surname together with a space and compares it to @Name returning matching results. Donnie 20/11/13 */ ALTER PROCEDURE [dbo].[pr_FindStudents] @ID nvarchar(50)='' , @Name nvarchar(100) , @IncludeInactiveStudents bit=0 , @Photos bit=0 , @GroupMask int=0 AS DECLARE @d datetime SET @d=[dbo].[GetDaymapDate]() DECLARE @StudentList TABLE ( [ID] int, StudentCode nvarchar(20), FullName nvarchar(1500), ClassID int, RoomCode nvarchar(255), SubjectCode nvarchar(255), Email nvarchar(255), Form nvarchar(100), EventID int, [Group] nvarchar(100), [Year] nvarchar(100), House nvarchar(100), PhotoID int, Photo image null, [Location] nvarchar(100) ) DECLARE @StudentListTemp TABLE ( [ID] int, StudentCode nvarchar(20), FullName nvarchar(1500), ClassID text, RoomCode nvarchar(255), SubjectCode nvarchar(255), Email nvarchar(255), Form nvarchar(100), EventID text, [Group] nvarchar(100), [Year] nvarchar(100), House nvarchar(100), PhotoID int, Photo image null, [Location] nvarchar(100) ) IF @Photos=0 BEGIN INSERT INTO @StudentList SELECT s.[ID], s.StudentCode, s.Firstname + ' ' + s.Surname, c.ClassID, dbo.EventRooms(cl.EventID) AS RoomCode, c.SubjectCode, s.Email, s.Form, cl.EventID, s.[Group], s.[Year], s.House, NULL As PhotoID, NULL As Photo, CONCAT(c.SubjectCode, ' ', dbo.EventRooms(cl.EventID)) FROM (SELECT PersonID AS StudentID, ClassID, ce.EventID FROM dbo.CalendarEvents ce INNER JOIN dbo.CalendarEventPeople cep ON cep.EventID=ce.EventID WHERE ce.StartTime <= @d AND ce.EndTime > @d AND cep.PersonType=2) cl INNER JOIN dbo.Class c ON cl.ClassID=c.ClassID RIGHT OUTER JOIN dbo.Students s ON cl.StudentID = s.[ID] WHERE ( ((@ID='') AND (s.Surname LIKE '%' + @Name + '%' OR s.Firstname LIKE '%' + @Name + '%' OR s.Firstname + ' ' + s.Surname LIKE '%' + @Name + '%' )) OR ((@ID<>'') AND (s.StudentCode=@ID)) ) AND (@IncludeInactiveStudents=1 OR ((s.Flags & 1)=1)) ORDER BY s.Surname END ELSE BEGIN INSERT INTO @StudentList SELECT s.[ID], s.StudentCode, s.Firstname + ' ' + s.Surname, c.ClassID, dbo.EventRooms(cl.EventID) AS RoomCode, c.SubjectCode, s.Email, s.Form, cl.EventID, s.[Group], s.[Year], s.House, p.StudentID AS PhotoID, p.Photo, CONCAT(c.SubjectCode, ' ', dbo.EventRooms(cl.EventID)) FROM (SELECT PersonID AS StudentID, ClassID, ce.EventID FROM dbo.CalendarEvents ce INNER JOIN dbo.CalendarEventPeople cep ON cep.EventID=ce.EventID WHERE ce.StartTime <= @d AND ce.EndTime > @d AND cep.PersonType=2) cl INNER JOIN dbo.Class c ON cl.ClassID=c.ClassID RIGHT OUTER JOIN dbo.Students s ON cl.StudentID = s.[ID] LEFT OUTER JOIN StudentPhotos p ON p.StudentID=s.[ID] WHERE ( ((@ID='') AND (s.Surname LIKE '%' + @Name + '%' OR s.Firstname LIKE '%' + @Name + '%' OR s.Firstname + ' ' + s.Surname LIKE '%' + @Name + '%' )) OR ((@ID<>'') AND (s.StudentCode=@ID)) ) AND (@IncludeInactiveStudents=1 OR ((s.Flags & 1)=1)) ORDER BY s.Surname END --Student notes SELECT s.[ID],snc.CategoryID,snc.Category,snc.Colour, sn.NoteID,sn.Updated FROM dbo.StudentNotes sn INNER JOIN @StudentList s ON sn.StudentID=s.[ID] INNER JOIN dbo.StudentNoteCategories snc ON snc.CategoryID=sn.CategoryID WHERE snc.Colour IS NOT NULL AND sn.Expiry>=dbo.DayFromDate(@d) AND ((@GroupMask & snc.[Read]) > 0 OR (@GroupMask & snc.[Edit] > 0)) AND snc.[Attendance] =0 ORDER BY s.[ID], snc.CategoryID, sn.Updated desc --Student list Insert into @StudentListTemp select ID, StudentCode, FullName, String_agg(ClassID,', ') as ClassID, String_agg(RoomCode,', ') as RoomCode, String_agg(SubjectCode,', ') as SubjectCode, Email, Form, String_agg(EventID,', ') as EventID, [Group], [Year], House, cast(PhotoID as varbinary(max)) as PhotoID, cast(Photo as varbinary(max)) as Photo, String_agg([Location], ', ') as [Location] from @StudentList Group by ID, StudentCode, FullName, [Year], Form, Email, [Group], House, cast(PhotoID as varbinary(max)), cast(Photo as varbinary(max)) Select * from @StudentListTemp
Editor is loading...