Database

18-Feb-15 16:18

Hi All,

The following stored procedure is working fine when I pass parameters using exec call, but when I do the same thing from UI, it is not fetching what I want.

Can some one please help me in that?

ALTER PROCEDURE [dbo].[ChildSearch_sel]
	(
	@UserId INT,
	@AllNamesPattern VARCHAR(250) = NULL,
	@ChildNamePattern VARCHAR(250) = NULL,
	@NicknamePattern VARCHAR(250) = NULL,
	@ChildBirthDate DATETIME = NULL,
	@SiteId INT = NULL,
	@ClassScheduleId INT = NULL,
	@ParentNamePattern VARCHAR(250) = NULL,
	@SchoolYearId INT = 3,
	@StaffMemberId INT = NULL,
	@ContractorId INT = NULL,
	@SubcontractorId INT = NULL,
	@ChildStatuses VARCHAR(20) = NULL,
	@Page INT = 1,
	@RowsPerPage INT = 20,
	@TotalRows INT OUT,
	@SortExpression VARCHAR(50) = 'ChildLastName',
	@SortDirection VARCHAR(15) = 'Ascending',
	@HasHealthFollowUpAlerts BIT = NULL,
	@ChildId INT = NULL
	)
	AS
BEGIN
	DECLARE	@Print VARCHAR(1000),
		@IsAdmin BIT = 0,
		@IsFss BIT = 0,
		@IsTeacher BIT = 0,
		@FilterFamilyMembers BIT = 0,
		@FilterStaff BIT = 0,
		@FamilyMemberIds IdTableType,
		@Filter_ChildIds IdTableType,
		@Nickname VARCHAR(250) = NULL,
		@Nickname_ChildIds IdTableType,
		@StaffIds IdTableType,
		@FssRoleIds IdTableType,
		@TeacherRoleIds IdTableType,
		@RequestedCPS IdTableType,
		@SkipOrgFilterCheck BIT = 0,
		@SchoolYearBeginYear INT,
		@SchoolYearStartDate DATE
		;

	DECLARE	@ROLE_FSS INT = 15,
		@ROLE_FSA INT = 14,
		@ROLE_LT INT = 17,
		@ROLE_AT INT = 1,
		@ROLE_OCS INT = 19,
		
		@PROGSTATUS_PRESCREEN INT = 1,
		@PROGSTATUS_NOPRESCREEN INT = 2,
		@PROGSTATUS_APPLICATION INT = 3,
		@PROGSTATUS_NOAPPLICATION INT = 4,
		@PROGSTATUS_ONWAITLIST INT = 5,
		@PROGSTATUS_ENROLLED INT = 6,
		@PROGSTATUS_PENDINGTRANSFER INT = 7,
		@PROGSTATUS_INCOMPLETEEXIT INT = 8,
		@PROGSTATUS_EXITED INT = 9,
		@PROGSTATUS_PENDINGEXIT INT = 11,
		
		@SRAL_LEADTEACHER INT = 30,
		@SRAL_ASSTEACHER INT = 29,
		
		@FLAG_PRESCREEN INT = 1,
		@FLAG_NOPRESCREEN INT = 2,
		@FLAG_APPLICATION INT = 4,
		@FLAG_NOAPPLICATION INT = 8,
		@FLAG_ONWAITLIST INT = 16,
		@FLAG_ENROLLED INT = 32,
		@FLAG_PENDINGTRANSFER INT = 64,
		@FLAG_INCOMPLETEEXIT INT = 128,
		@FLAG_EXITED INT = 256,
		@FLAG_PENDINGEXIT INT = 512,
		@FLAG_EVERENROLLED INT = 1024;
		
	SELECT	@IsAdmin = dbo.fnIsAdmin(@UserId) | dbo.fnIsDelStaff(@UserId);

	IF @SiteId IS NOT NULL OR @SubcontractorId IS NOT NULL
		SELECT	@ContractorId = NULL;
	IF @SiteId IS NOT NULL
		SELECT	@SubcontractorId = NULL;

	IF @SiteId IS NULL AND @SubcontractorId IS NULL AND @ContractorId IS NULL
		SELECT	@SkipOrgFilterCheck = 1;

	DECLARE	@OrganizationFilter IdTableType,
		@NonSecureOrganizationRelationshipIds IdTableType,
		@SecurityOrganizationRelationshipIds IdTableType;

	IF @SkipOrgFilterCheck = 0
	BEGIN
		PRINT 'Pulling orgs (NOT SECURE) ...';
		INSERT	INTO @NonSecureOrganizationRelationshipIds
		SELECT	OrganizationRelationshipId
		FROM	dbo.fnFilterOrganizationRelationshipsWithExcludeOption(@UserId, @SchoolYearId, 0); --Do not exclude organizations where a user only has child level access.  Security is handled when filtering children

		PRINT 'Filtering orgs (NOT SECURE) check against supplied parameters for future filtering needs in sproc (after children pulled from security check) ...';
		INSERT	INTO @OrganizationFilter
		SELECT	ID
		FROM	dbo.fnProcessOrganizationRelationshipsByType(@UserId, @SchoolYearId, @ContractorId, @SubcontractorId, @SiteId, 'ALL', @NonSecureOrganizationRelationshipIds);
	END;

	SELECT	@Print = 'Parse requested CPS (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
	RAISERROR(@Print, 10, 1) WITH NOWAIT;
	INSERT	INTO @RequestedCPS
	SELECT	CONVERT(INT, Element)
	FROM	dbo.fnParseString(',', @ChildStatuses);

	--TODO: Have the filtering below for both FM and Staff bring in SchoolYear to limit the results.
	IF @AllNamesPattern IS NOT NULL OR @ChildNamePattern IS NOT NULL OR @ParentNamePattern IS NOT NULL	
	BEGIN
		SELECT	@FilterFamilyMembers = 1;
		
		DECLARE @FamilyMemberSearchString VARCHAR(250) = NULL;		
		
		IF @AllNamesPattern IS NOT NULL
			SELECT	@FamilyMemberSearchString = '%' + @AllNamesPattern + '%';
		ELSE IF @ChildNamePattern IS NOT NULL
			SELECT	@FamilyMemberSearchString = '%' + @ChildNamePattern + '%';
		ELSE IF @ParentNamePattern IS NOT NULL
			SELECT	@FamilyMemberSearchString = '%' + @ParentNamePattern + '%';							
			
		IF @AllNamesPattern IS NOT NULL
		BEGIN
			SELECT	@Print = 'Filtering name information from FamilyMember (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
			RAISERROR(@Print, 10, 1) WITH NOWAIT;
			INSERT	INTO @FamilyMemberIds
			SELECT	FamilyMemberId
			FROM	FamilyMember
			WHERE	IsDeleted = 0
			AND	(
					RTRIM(LTRIM(LegalFirstName)) LIKE @FamilyMemberSearchString
				OR	RTRIM(LTRIM(LegalLastName)) LIKE @FamilyMemberSearchString
				OR	RTRIM(LTRIM(LegalFirstName)) + ' ' + RTRIM(LTRIM(LegalLastName)) LIKE @FamilyMemberSearchString
				);
		END;
		ELSE IF @ChildNamePattern IS NOT NULL
		BEGIN
			SELECT	@Print = 'Filtering name information from FamilyMember (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
			RAISERROR(@Print, 10, 1) WITH NOWAIT;
			INSERT	INTO @FamilyMemberIds
			SELECT	DISTINCT
				FM.FamilyMemberId
			FROM	FamilyMember FM
				INNER JOIN Child C
					ON C.FamilyMemberId = FM.FamilyMemberId
			WHERE	FM.IsDeleted = 0
			AND	(
					RTRIM(LTRIM(FM.LegalFirstName)) LIKE @FamilyMemberSearchString
				OR	RTRIM(LTRIM(FM.LegalLastName)) LIKE @FamilyMemberSearchString
				OR	RTRIM(LTRIM(FM.LegalFirstName)) + ' ' + RTRIM(LTRIM(FM.LegalLastName)) LIKE @FamilyMemberSearchString
				)
		END;
		ELSE IF @ParentNamePattern IS NOT NULL
		BEGIN
			SELECT	@Print = 'Filtering name information from FamilyMember (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
			RAISERROR(@Print, 10, 1) WITH NOWAIT;
			INSERT	INTO @FamilyMemberIds
			SELECT	FM.FamilyMemberId
			FROM	FamilyMember FM
			WHERE	FM.IsDeleted = 0
			AND	NOT EXISTS	(
							SELECT	1
							FROM	Child
							WHERE	FamilyMemberId = FM.FamilyMemberId
							)
			AND	(
					RTRIM(LTRIM(FM.LegalFirstName)) LIKE @FamilyMemberSearchString
				OR	RTRIM(LTRIM(FM.LegalLastName)) LIKE @FamilyMemberSearchString
				OR	RTRIM(LTRIM(FM.LegalFirstName)) + ' ' + RTRIM(LTRIM(FM.LegalLastName)) LIKE @FamilyMemberSearchString
				);
		END;				
		
		SELECT	@Print = 'Moving FamilyMember matches to child (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		MERGE	@FamilyMemberIds IDS
		USING	Child C
		ON	(C.FamilyMemberId = IDS.ID)
		
		WHEN MATCHED
			THEN DELETE
			
		OUTPUT
			C.ChildId
			INTO @Filter_ChildIds;
				
		SELECT	@Print = 'Pulling ChildId from FamilyMembers (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		INSERT	INTO @Filter_ChildIds
		SELECT	DISTINCT
			FAM.ChildId
		FROM	@FamilyMemberIds IDS
			INNER JOIN HouseholdFamilyMember HFM
				ON HFM.IsDeleted = 0
				AND HFM.FamilyMemberId = IDS.ID
			INNER JOIN Household HSHLD
				ON HSHLD.HouseholdId = HFM.HouseholdId
			INNER JOIN Family FAM
				ON FAM.FamilyId = HSHLD.FamilyId
		WHERE	NOT EXISTS	(
							SELECT	1
							FROM	@Filter_ChildIds
							WHERE	ID = FAM.ChildId
							);
	END;

	IF @AllNamesPattern IS NOT NULL OR @StaffMemberId IS NOT NULL
	BEGIN
		SELECT	@FilterStaff = 1;
		
		DECLARE @StaffSearchString VARCHAR(250) = NULL;
		
		IF @AllNamesPattern IS NOT NULL
			SELECT	@StaffSearchString = '%' + @AllNamesPattern + '%';
		--ELSE IF @StaffName IS NOT NULL
		--	SELECT	@StaffSearchString = '%' + @StaffName + '%';
			
		SELECT	@Print = 'Filtering name information from StaffMember (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		INSERT	INTO @StaffIds
		SELECT	StaffMemberid
		FROM	StaffMember
		WHERE	IsDeleted = 0
		AND	(
				@StaffMemberId = StaffMemberId
			OR	(
					@StaffMemberId IS NULL
				AND	(
						RTRIM(LTRIM(FirstName)) LIKE @StaffSearchString
					OR	RTRIM(LTRIM(LastName)) LIKE @StaffSearchString
					OR	RTRIM(LTRIM(FirstName)) + ' ' + RTRIM(LTRIM(LastName)) LIKE @StaffSearchString
					)
				)
			);
		
		SELECT	@Print = 'Pulling ChildId from Family Support staff (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		INSERT	INTO @Filter_ChildIds
		SELECT	DISTINCT
			CCLS.ChildId
		FROM	@StaffIds IDS
			INNER JOIN ChildClass CCLS
				ON CCLS.FamilySupportSpecialistStaffId = IDS.ID
				AND CCLS.IsDeleted = 0
		WHERE	NOT EXISTS	(
							SELECT	1
							FROM	@Filter_ChildIds
							WHERE	ID = CCLS.ChildId
							);
	
		
		SELECT	@Print = 'Pulling ChildId from Teaching staff (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		INSERT	INTO @Filter_ChildIds
		SELECT	DISTINCT
			CCLS.ChildId
		FROM	@StaffIds IDS
			INNER JOIN StaffClass STFCLS
				ON STFCLS.StaffMemberId = IDS.ID
				AND STFCLS.IsDeleted = 0
			INNER JOIN ChildClass CCLS
				ON CCLS.IsDeleted = 0
				AND CCLS.ClassScheduleId = STFCLS.ClassScheduleId
		WHERE	NOT EXISTS	(
							SELECT	1
							FROM	@Filter_ChildIds
							WHERE	ID = CCLS.ChildId
							);
	END;
	
	IF @NicknamePattern IS NOT NULL
	BEGIN
		SET @Nickname= '' 			
		INSERT INTO @Nickname_ChildIds
		SELECT DISTINCT ChildId FROM Child C
		WHERE Nickname LIKE @NicknamePattern AND IsDeleted=0
		SELECT * FROM @Nickname_ChildIds		
	END	
		
	DECLARE	@Security_VisibleChildIds IdTableType;

	IF @IsAdmin = 0
	BEGIN
		SELECT	@Print = 'Non-Admin -- pulling children visible from security (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		INSERT	INTO @Security_VisibleChildIds
		SELECT	ChildId
		FROM	dbo.fnFilterChildren(@UserId, @SchoolYearId);
	END;
		
	CREATE TABLE #PreResults
		(
		ChildId INT NOT NULL PRIMARY KEY CLUSTERED,
		ChildFirstName VARCHAR(150) NOT NULL,
		ChildLastName VARCHAR(150) NOT NULL,
		CHildNickname VARCHAR(250) NULL, 
		ChildAge INT NOT NULL,
		SiteIds VARCHAR(250) NULL,
		Site VARCHAR(500) NULL,
		ClassScheduleId INT NULL,
		Class VARCHAR(500) NULL,
		TeacherIds VARCHAR(75) NULL,
		TeacherName VARCHAR(500) NULL,
		ChildStatusId INT NULL,
		ChildStatus VARCHAR(50) NULL,
		HasAlerts BIT NOT NULL DEFAULT(0),
		IsExpired BIT NOT NULL DEFAULT(0),
		IsReadOnly BIT NOT NULL DEFAULT(0),
		PrescreenId INT NULL,
		ApplicationDataId INT NULL,
		ChildStatusFlags INT NOT NULL,
		PriorityPoints INT NULL,
		SortKey VARCHAR(100) NOT NULL,
		ClassStarted BIT NULL DEFAULT(0)
		);
		
	CREATE INDEX #IX_PreResults_SortKey ON #PreResults (SortKey);
		
	--Mirror #PreResults table schema into #Results
	SELECT	*
		INTO #Results
	FROM	#PreResults;
	
	ALTER TABLE #Results
		ADD PRIMARY KEY CLUSTERED (ChildId);

	--Get School Start Date
	SELECT @SchoolYearBeginYear = DatePart(year,BeginDate)
	FROM SchoolYear 
	WHERE SchoolYearId = @SchoolYearId

	SET @SchoolYearStartDate = '08/31/' + CONVERT(VARCHAR,@SchoolYearBeginYear);
		
	SELECT	@Print = 'Pulling initial list of children (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
	RAISERROR(@Print, 10, 1) WITH NOWAIT;
	INSERT	INTO #PreResults
		(ChildId, ChildFirstName, ChildLastName, CHildNickname, ChildAge, ChildStatusFlags, SortKey)
	SELECT	C.ChildId,
		FM.LegalFirstName,
		FM.LegalLastName,
		C.Nickname,
		dbo.fnCalculateAge(FM.BirthDate,@SchoolYearStartDate) as ChildAge,
		CPS.StatusFlag,
		CASE @SortExpression
				WHEN 'ChildFirstName' THEN LTRIM(FM.LegalFirstName) + CONVERT(VARCHAR, C.ChildId)
				WHEN 'ChildLastName' THEN LTRIM(FM.LegalLastName) + CONVERT(VARCHAR, C.ChildId)
				WHEN 'ChildAge' THEN CONVERT(VARCHAR,CONVERT(DATE,FM.BirthDate))
				WHEN 'ChildStatus' THEN RIGHT('0000000000' + CONVERT(VARCHAR, CPS.StatusFlag), 10) + CONVERT(VARCHAR, C.ChildId)
				ELSE RIGHT('0000000000' + CONVERT(VARCHAR, C.ChildId), 10)
			END AS SortKey
	FROM	Child C
		INNER JOIN FamilyMember FM
			ON FM.FamilyMemberId = C.FamilyMemberId
		INNER JOIN vwChildProgramStatus CPS
			ON CPS.ChildId = C.ChildId
			AND CPS.SchoolYearId = @SchoolYearId
	WHERE	C.IsDeleted = 0
	AND	(
			@ChildId IS NULL
		OR	C.ChildId = @ChildId
		)
	AND	(
			@SkipOrgFilterCheck = 1
		OR	EXISTS	(
					SELECT	1
					FROM	vwChildOrganization CHILDORG WITH (NOEXPAND)
					WHERE	CHILDORG.SchoolYearId = @SchoolYearId
					AND	CHILDORG.ChildId = C.ChildId
					AND	EXISTS	(
								SELECT	1
								FROM	@OrganizationFilter
								WHERE	ID = CHILDORG.OrganizationRelationshipId
								)
					)
		)
	AND	(
			@ClassScheduleId IS NULL
		OR	EXISTS	(
					SELECT	1
					FROM	ChildClass CCLS
					WHERE	CCLS.ClassScheduleId = @ClassScheduleId
					AND	CCLS.IsExited = 0
					AND	CCLS.IsActive = 1
					AND	CCLS.IsDeleted = 0
					AND	CCLS.ChildId = C.ChildId
					)
		)
	AND	(
			(
				@IsAdmin = 1
			AND	EXISTS	(
						SELECT	1
						FROM	vwChildProgramStatus WITH (NOEXPAND)
						WHERE	SchoolYearId = @SchoolYearId
						AND	ChildId = C.ChildId
						)
			)
		OR	(
				@IsAdmin = 0
			AND	EXISTS	(
						SELECT	1
						FROM	@Security_VisibleChildIds
						WHERE	ID = C.ChildId
						)
			)
		)
	AND	(
			@ChildBirthDate IS NULL
		OR	FM.BirthDate = @ChildBirthDate
		)
	AND	(
			(
				@FilterFamilyMembers = 0
			AND	@FilterStaff = 0
			)
		OR	EXISTS	(
					SELECT	1
					FROM	@Filter_ChildIds
					WHERE	ID = C.ChildId
					)
		)
	AND	(
		NOT EXISTS	(
					SELECT	1
					FROM	@Nickname_ChildIds
					)
		OR	
		EXISTS	(
					SELECT	1
					FROM	@Nickname_ChildIds NC
					WHERE	 NC.ID = C.ChildId
					)
		);

	SELECT	@TotalRows = @@ROWCOUNT;

	SET NOCOUNT ON;

	DECLARE	@ReturnEnrolled TINYINT = 0,
		@ReturnWaitListed TINYINT = 0,
		@ReturnExited TINYINT = 0,
		@ReturnPendingExit TINYINT = 0,
		@ReturnPendingTransfer TINYINT = 0,
		@ReturnIncompleteExit TINYINT = 0,
		@ReturnCompleteApp TINYINT = 0,
		@ReturnIncompleteApp TINYINT = 0,
		@ReturnCompletePre TINYINT = 0,
		@ReturnIncompletePre TINYINT = 0,
		
		@AlwaysExcludeEnrolled TINYINT = 0;
		
	IF EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_NOPRESCREEN)
		SELECT	@ReturnIncompletePre = 1;
	ELSE IF EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_PRESCREEN)
		SELECT	@ReturnCompletePre = 1;

	ELSE IF EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_NOAPPLICATION)
		SELECT	@ReturnIncompleteApp = 1;
	ELSE IF EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_APPLICATION)
		SELECT	@ReturnCompleteApp = 1;
		
	IF EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_ONWAITLIST)
		SELECT	@ReturnWaitListed = 1;

	SELECT	@AlwaysExcludeEnrolled =
		CASE WHEN @ReturnIncompletePre + @ReturnCompletePre + @ReturnIncompleteApp + @ReturnCompleteApp > 0 THEN 1 ELSE 0 END

	--The stuff above is exclusive; if they select one of those, they want kids that are just in that status.  The ones
	--below can be inclusive.
	IF @AlwaysExcludeEnrolled = 0
	BEGIN
		IF @ReturnWaitListed = 0 AND EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_ENROLLED)
			SELECT	@ReturnEnrolled = 1;
		ELSE
		BEGIN
			IF @ReturnWaitListed = 0 AND EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_PENDINGEXIT)
				SELECT	@ReturnPendingExit = 1;

			IF @ReturnWaitListed = 0 AND EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_PENDINGTRANSFER)
				SELECT	@ReturnPendingTransfer = 1;

			IF @ReturnWaitListed = 0 AND EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_INCOMPLETEEXIT)
				SELECT	@ReturnIncompleteExit = 1;
		END;

		IF EXISTS (SELECT 1 FROM @RequestedCPS WHERE ID = @PROGSTATUS_EXITED)
			SELECT	@ReturnExited = 1;
	END;

	SET NOCOUNT OFF;

	IF EXISTS (SELECT 1 FROM @RequestedCPS)
	BEGIN
		SELECT	@Print = 'Applying CPS filter(s) (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		DELETE	RSLT
		FROM	#PreResults RSLT
		WHERE	(
					@ReturnIncompletePre = 0
				OR	@ReturnWaitListed = 1
				OR	(
						@ReturnIncompletePre = 1
					AND	@ReturnWaitListed = 0
					AND	(
							ChildStatusFlags & @FLAG_NOPRESCREEN = 0 --Remove anyone that does not have an incomplete prescreen
						OR	ChildStatusFlags & (@FLAG_PRESCREEN | @FLAG_APPLICATION | @FLAG_NOAPPLICATION | @FLAG_ONWAITLIST | @FLAG_ENROLLED | @FLAG_EXITED) > 0 --Remove anyone that is past incomplete prescreen
						)
					)
				)
		AND	(
				@ReturnCompletePre = 0
			OR	@ReturnWaitListed = 1
			OR	(
					@ReturnCompletePre = 1
				AND	@ReturnWaitListed = 0
				AND	(
						ChildStatusFlags & @FLAG_PRESCREEN = 0 --Remove anyone that does not have a complete prescreeb
					OR	ChildStatusFlags & (@FLAG_APPLICATION | @FLAG_NOAPPLICATION | @FLAG_ONWAITLIST | @FLAG_ENROLLED | @FLAG_EXITED) > 0 --Remove anyone that is past complete prescreen
					)
				)
			)
		AND	(
				@ReturnIncompleteApp = 0
			OR	@ReturnWaitListed = 1
			OR	(
					@ReturnIncompleteApp = 1
				AND	@ReturnWaitListed = 0
				AND	(
						ChildStatusFlags & @FLAG_NOAPPLICATION = 0 --Remove anyone that does not have an incomplete application
					OR	ChildStatusFlags & (@FLAG_APPLICATION | @FLAG_ONWAITLIST | @FLAG_ENROLLED | @FLAG_EXITED) > 0 --Remove anyone that is past incomplete application
					)
				)
			)
		AND	(
				@ReturnCompleteApp = 0
			OR	@ReturnWaitListed = 1
			OR	(
					@ReturnCompleteApp = 1
				AND	@ReturnWaitListed = 0
				AND	(
						ChildStatusFlags & @FLAG_APPLICATION = 0 --Remove anyone that does not have a complete application
					OR	ChildStatusFlags & (@FLAG_ONWAITLIST | @FLAG_ENROLLED | @FLAG_EXITED) > 0 --Remove anyone that is past complete application
					)
				)
			)

		--Wait List AND Pre/App
		AND	(
					@ReturnWaitListed = 0
			OR	(
					@ReturnCompleteApp = 0
				AND	@ReturnIncompleteApp = 0
				AND	@ReturnCompletePre = 0
				AND	@ReturnIncompletePre = 0
				)
			OR	(
					@ReturnWaitListed = 1
				AND	(@ReturnCompleteApp = 1 OR @ReturnIncompleteApp = 1 OR @ReturnCompletePre = 1 OR @ReturnIncompletePre = 1)
				AND	(
						ChildStatusFlags & (@FLAG_ONWAITLIST | 
							CASE WHEN @ReturnIncompletePre = 1 THEN @FLAG_NOPRESCREEN ELSE 0 END |
							CASE WHEN @ReturnCompletePre = 1 THEN @FLAG_PRESCREEN ELSE 0 END |
							CASE WHEN @ReturnIncompleteApp = 1 THEN @FLAG_NOAPPLICATION ELSE 0 END |
							CASE WHEN @ReturnCompleteApp = 1 THEN @FLAG_APPLICATION ELSE 0 END
							) != (@FLAG_ONWAITLIST | 
								CASE WHEN @ReturnIncompletePre = 1 THEN @FLAG_NOPRESCREEN ELSE 0 END |
								CASE WHEN @ReturnCompletePre = 1 THEN @FLAG_PRESCREEN ELSE 0 END |
								CASE WHEN @ReturnIncompleteApp = 1 THEN @FLAG_NOAPPLICATION ELSE 0 END |
								CASE WHEN @ReturnCompleteApp = 1 THEN @FLAG_APPLICATION ELSE 0 END
								)
					OR	ChildStatusFlags & (@FLAG_ENROLLED | @FLAG_EXITED) > 0 --Remove anyone that is past wait list
					)
				)
			)
			
		--JUST Wait List
		AND	(
				@ReturnWaitListed = 0
			OR	(@ReturnCompleteApp = 1 OR @ReturnIncompleteApp = 1 OR @ReturnCompletePre = 1 OR @ReturnIncompletePre = 1)
			OR	(
					@ReturnWaitListed = 1
				AND	ChildStatusFlags & @FLAG_ONWAITLIST = 0
				)
			)
		AND	(
				@ReturnEnrolled = 0
			OR	(
					@ReturnEnrolled = 1
				AND	ChildStatusFlags & @FLAG_ENROLLED = 0
				)
			)
		AND	(
				@ReturnExited = 0
			OR	(
					@ReturnExited = 1
				AND	ChildStatusFlags & @FLAG_EXITED = 0
				)
			)
		AND	(
				@ReturnPendingExit = 0
			OR	(
					@ReturnPendingExit = 1
				AND	ChildStatusFlags & @FLAG_PENDINGEXIT = 0
				)
			)
		AND	(
				@ReturnPendingTransfer = 0
			OR	(
					@ReturnPendingTransfer = 1
				AND	ChildStatusFlags & @FLAG_PENDINGTRANSFER = 0
				)
			)
		AND	(
				@ReturnIncompleteExit = 0
			OR	(
					@ReturnIncompleteExit = 1
				AND	ChildStatusFlags & @FLAG_INCOMPLETEEXIT = 0
				)
			);
	END;

	SELECT	@TotalRows = @TotalRows - @@ROWCOUNT;

	PRINT 'Setting HasAlerts flag for children ...';
	UPDATE	RSLT
	SET	HasAlerts = 1
	FROM	#PreResults RSLT
	WHERE	EXISTS	(
					SELECT	1
					FROM	ChildHealthFollowUpAlert
					WHERE	ChildId = RSLT.ChildId
					AND	IsDeleted = 0
					AND	SchoolYearId = @SchoolYearId
					AND	GETDATE() BETWEEN EffectiveDate AND COALESCE(ExpirationDate, CONVERT(DATETIME, '12/31/9999'))
					);

	IF @HasHealthFollowUpAlerts IS NOT NULL
	BEGIN
		SELECT	@Print = 'Filter by passed in @HasHealthFollowUpAlerts (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		DELETE
		FROM	#PreResults
		WHERE	HasAlerts != @HasHealthFollowUpAlerts;

		SELECT	@TotalRows = @TotalRows - @@ROWCOUNT;
	END;

	--Check for users who have children on their list that are in the middle of a records transfer, and the contractor of the
	--user querying the data is the "to contractor" in that records transfer.  Those kids will get short-circuited so the new
	--contractor cannot exit, transfer, etc.
	IF @IsAdmin = 0
	BEGIN
		DECLARE	@OrganizationRelationshipIdsUserHasAccessTo IdTableType;
		INSERT	INTO @OrganizationRelationshipIdsUserHasAccessTo
		SELECT	OrganizationRelationshipId
		FROM	dbo.fnFilterOrganizationRelationships(@UserId, @SchoolYearId);

		UPDATE	RSLT
		SET	ChildStatusFlags = RSLT.ChildStatusFlags ^
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_PENDINGTRANSFER = 0 THEN 0 ELSE @FLAG_PENDINGTRANSFER END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_PENDINGEXIT = 0 THEN 0 ELSE @FLAG_PENDINGEXIT END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_INCOMPLETEEXIT = 0 THEN 0 ELSE @FLAG_INCOMPLETEEXIT END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_ENROLLED = 0 THEN 0 ELSE @FLAG_ENROLLED END ^
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_EXITED = 0 THEN 0 ELSE @FLAG_EXITED END ^
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_ONWAITLIST = 0 THEN 0 ELSE @FLAG_ONWAITLIST END,
			IsReadOnly = 1
		FROM	#PreResults RSLT
			INNER JOIN ChildTransferRequest RECTRANSFER
				ON RECTRANSFER.IsDeleted = 0
				AND RECTRANSFER.ChildId = RSLT.ChildId
				AND RECTRANSFER.ReadyToTransfer = 1	--Records transfer has occurred
				AND RECTRANSFER.IsProcessed = 0		--Classroom Transfer *or* Add to Waiting List has not occurred
			INNER JOIN @OrganizationRelationshipIdsUserHasAccessTo ORGSACCESS
				ON ORGSACCESS.ID = RECTRANSFER.ToOrganizationRelationshipId;
	END;

	--Note: if we're not sorting by site, class, or teacher, we can do paging right now and save a lot of processing
	--trying to create those concatenated lists!
	IF @TotalRows > @RowsPerPage AND @SortExpression NOT IN ('TeacherName', 'Class', 'Site')
	BEGIN
		--Paging: http://www.4guysfromrolla.com/webtech/042606-1.shtml
			--Also, maybe consider: http://stackoverflow.com/questions/3212692/sql-performance-where-vs-whererow-number/3215973#3215973
		DECLARE	@SortKeyToStart VARCHAR(100),
			@StartRow INT = (@RowsPerPage * (@Page - 1) + 1);

		SET ROWCOUNT @StartRow;
		SELECT	@SortKeyToStart = SortKey
		FROM	#PreResults
		ORDER BY
			CASE WHEN @SortDirection = 'Descending' THEN
				SortKey
			ELSE
				NULL
			END DESC,
			CASE WHEN @SortDirection = 'Ascending' THEN
				SortKey
			ELSE
				NULL
			END ASC;

		SET ROWCOUNT @RowsPerPage;

		INSERT	INTO #Results
		SELECT	*
		FROM	#PreResults
		WHERE	(
					@SortDirection = 'Ascending'
				AND	SortKey >= @SortKeyToStart
				)
		OR	(
				@SortDirection = 'Descending'
			AND	SortKey <= @SortKeyToStart
			)
		ORDER BY
			CASE WHEN @SortDirection = 'Descending' THEN
				SortKey
			ELSE
				NULL
			END DESC,
			CASE WHEN @SortDirection = 'Ascending' THEN
				SortKey
			ELSE
				NULL
			END ASC;

		SET ROWCOUNT 0;
	END;
	ELSE
		INSERT	INTO #Results
		SELECT	*
		FROM	#PreResults;
	
	IF @IsAdmin = 0
	BEGIN
		DECLARE	@OrgsWithChildOrGreaterAccess IdTableType,
			@INCLUDECHILDACCESS BIT = 0;
		INSERT	INTO @OrgsWithChildOrGreaterAccess
		SELECT	OrganizationRelationshipId
		FROM	dbo.fnFilterOrganizationRelationshipsWithExcludeOption(@UserId, @SchoolYearId, @INCLUDECHILDACCESS);
		
		--Force the expired kids (that were enrolled or exited) to exited
		WITH CTE AS
			(
			SELECT	RSLT.ChildId
			FROM	#Results RSLT
				INNER JOIN ChildOrganization CHILDORG
					ON CHILDORG.ChildId = RSLT.ChildId
					AND CHILDORG.IsDeleted = 0
				INNER JOIN OrganizationRelationship ORGREL
					ON ORGREL.OrganizationRelationshipId = CHILDORG.OrganizationRelationshipId
					AND ORGREL.IsDeleted = 0
					AND ORGREL.IsActive = 1
					AND ORGREL.SchoolYearId = @SchoolYearId
				INNER JOIN @OrgsWithChildOrGreaterAccess SEC
					ON SEC.ID = CHILDORG.OrganizationRelationshipId
			WHERE	RSLT.ChildStatusFlags & (@FLAG_ENROLLED | @FLAG_EXITED) > 0
			GROUP BY
				RSLT.ChildId
			HAVING
				MAX(CASE WHEN GETDATE() BETWEEN CHILDORG.EffectiveDate AND COALESCE(CHILDORG.ExpirationDate, '12/31/9999') THEN 0 ELSE 1 END) > 0
			AND	MAX(CASE WHEN GETDATE() BETWEEN CHILDORG.EffectiveDate AND COALESCE(CHILDORG.ExpirationDate, '12/31/9999') THEN 1 ELSE 0 END) = 0
			)
		UPDATE	RSLT
		SET	ChildStatusFlags = RSLT.ChildStatusFlags ^
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_ONWAITLIST = 0 THEN 0 ELSE @FLAG_ONWAITLIST END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_PENDINGTRANSFER = 0 THEN 0 ELSE @FLAG_PENDINGTRANSFER END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_PENDINGEXIT = 0 THEN 0 ELSE @FLAG_PENDINGEXIT END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_INCOMPLETEEXIT = 0 THEN 0 ELSE @FLAG_INCOMPLETEEXIT END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_ENROLLED = 0 THEN 0 ELSE @FLAG_ENROLLED END | @FLAG_EXITED,
			IsExpired = 1
		FROM	#Results RSLT
			INNER JOIN CTE
				ON CTE.ChildId = RSLT.ChildId
		WHERE	RSLT.ChildStatusFlags & (@FLAG_ENROLLED | @FLAG_EXITED) > 0;
		
		--Force the expired kids (that were NEVER enrolled or exited) to complete prescreen/incomplete prescreen/complete app/incomplete app
		WITH CTE AS
			(
			SELECT	RSLT.ChildId
			FROM	#Results RSLT
				INNER JOIN ChildOrganization CHILDORG
					ON CHILDORG.ChildId = RSLT.ChildId
					AND CHILDORG.IsDeleted = 0
				INNER JOIN OrganizationRelationship ORGREL
					ON ORGREL.OrganizationRelationshipId = CHILDORG.OrganizationRelationshipId
					AND ORGREL.IsDeleted = 0
					AND ORGREL.IsActive = 1
					AND ORGREL.SchoolYearId = @SchoolYearId
				INNER JOIN @OrgsWithChildOrGreaterAccess SEC
					ON SEC.ID = CHILDORG.OrganizationRelationshipId
			WHERE	RSLT.ChildStatusFlags & (@FLAG_ENROLLED | @FLAG_EXITED) = 0
			GROUP BY
				RSLT.ChildId
			HAVING
				MAX(CASE WHEN GETDATE() BETWEEN CHILDORG.EffectiveDate AND COALESCE(CHILDORG.ExpirationDate, '12/31/9999') THEN 0 ELSE 1 END) > 0
			AND	MAX(CASE WHEN GETDATE() BETWEEN CHILDORG.EffectiveDate AND COALESCE(CHILDORG.ExpirationDate, '12/31/9999') THEN 1 ELSE 0 END) = 0
			)
		UPDATE	RSLT
		SET	ChildStatusFlags = RSLT.ChildStatusFlags ^
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_ONWAITLIST = 0 THEN 0 ELSE @FLAG_ONWAITLIST END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_PENDINGTRANSFER = 0 THEN 0 ELSE @FLAG_PENDINGTRANSFER END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_PENDINGEXIT = 0 THEN 0 ELSE @FLAG_PENDINGEXIT END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_INCOMPLETEEXIT = 0 THEN 0 ELSE @FLAG_INCOMPLETEEXIT END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_EXITED = 0 THEN 0 ELSE @FLAG_EXITED END ^ 
			CASE WHEN RSLT.ChildStatusFlags & @FLAG_ENROLLED = 0 THEN 0 ELSE @FLAG_ENROLLED END,
			IsExpired = 1
		FROM	#Results RSLT
			INNER JOIN CTE
				ON CTE.ChildId = RSLT.ChildId
		WHERE	RSLT.ChildStatusFlags & (@FLAG_ENROLLED | @FLAG_EXITED) = 0;
	END;

	CREATE TABLE #NameConcat
		(
		AutoId INT NOT NULL IDENTITY(1, 1),
		ChildId INT NOT NULL,
		ClassScheduleId INT NULL,
		SiteIds VARCHAR(250) NOT NULL,
		TeacherIds VARCHAR(250) NULL,
		ClassName VARCHAR(150),
		SiteName VARCHAR(1000),
		TeacherName VARCHAR(1000) NULL,
		TeacherSortKey TINYINT DEFAULT(1),
		ClassStarted BIT NULL DEFAULT(0),
		SchoolYearId INT NOT NULL,
		PRIMARY KEY CLUSTERED (ChildId, AutoId, TeacherSortKey)
		);

	IF NOT EXISTS (SELECT 1 FROM @RequestedCPS) OR (@ReturnEnrolled +  @ReturnPendingExit + @ReturnPendingTransfer + @ReturnIncompleteExit) > 0
	BEGIN
		SELECT	@Print = 'Pull sites, classrooms, and teachers for enrolled children (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		INSERT	INTO #NameConcat
			(ChildId, ClassScheduleId, SiteIds, ClassName, SiteName, TeacherIds, TeacherName, TeacherSortKey, ClassStarted, SchoolYearId)
		SELECT	RSLT.ChildId,
			SCHED.ClassScheduleId,
			SCHED.OrganizationRelationshipId AS SiteId,
			SCHED.Name AS ClassName,
			SITE.Name AS SiteName,
			CLASSINFO.TeacherId,
			CLASSINFO.TeacherName,
			CASE
				WHEN CLASSINFO.IsLeadTeacher IS NULL THEN 4
				WHEN CLASSINFO.IsLeadTeacher = 1 THEN 2
				ELSE 3
			END AS TeacherSortKey,
			CASE WHEN SCHED.StartDate <= CONVERT(VARCHAR, GETDATE(), 101) THEN 1 ELSE 0 END AS ClassStarted,
			SITE.SchoolYearId
		FROM	#Results RSLT
			INNER JOIN ChildClass CCLS
				ON CCLS.IsDeleted = 0
				AND CCLS.IsActive = 1
				AND CCLS.IsExited = 0
				AND CCLS.ChildId = RSLT.ChildId
			INNER JOIN ClassSchedule SCHED
				ON SCHED.ClassScheduleId = CCLS.ClassScheduleId
			INNER JOIN OrganizationRelationship SITE
				ON SITE.OrganizationRelationshipId = SCHED.OrganizationRelationshipId
			LEFT OUTER JOIN vwClassTeachersPresentForAllHours CLASSINFO --WITH (NOEXPAND)
				ON CLASSINFO.ClassScheduleId = CCLS.ClassScheduleId
		WHERE	RSLT.IsExpired = 0
		AND	(RSLT.ChildStatusFlags & @FLAG_ENROLLED) > 0
		ORDER BY
			ChildId,
			TeacherSortKey;
	END;

	IF NOT EXISTS (SELECT 1 FROM @RequestedCPS) OR
		((@ReturnEnrolled +  @ReturnPendingExit + @ReturnPendingTransfer + @ReturnIncompleteExit) = 0 AND (@ReturnWaitListed + @ReturnExited) > 0)
	BEGIN
		SELECT	@Print = 'Pull sites for wait listed children (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
		RAISERROR(@Print, 10, 1) WITH NOWAIT;
		INSERT	INTO #NameConcat
			(ChildId, SiteIds, SiteName, SchoolYearId)
		SELECT	RSLT.ChildId,
			ORGREL.OrganizationRelationshipId,
			ORGREL.Name,
			ORGREL.SchoolYearId
		FROM	#Results RSLT
			INNER JOIN ChildWaitList CWL
				ON CWL.IsDeleted = 0
				AND CWL.ChildId = RSLT.ChildId
			INNER JOIN WaitList WL
				ON WL.WaitListId = CWL.WaitListId
				AND WL.IsDeleted = 0
			INNER JOIN OrganizationRelationship ORGREL
				ON ORGREL.OrganizationRelationshipId = WL.SiteId
				AND ORGREL.SchoolYearId = @SchoolYearId
				AND ORGREL.IsDeleted = 0
		WHERE	RSLT.IsExpired = 0
		AND	(RSLT.ChildStatusFlags & (@FLAG_ONWAITLIST | @FLAG_ENROLLED)) > 0
		AND	NOT EXISTS	(
						SELECT	1
						FROM	#NameConcat
						WHERE	ChildId = RSLT.ChildId
						);
	END;

	--Quirky update
	DECLARE	@PrevChildId INT,
		@SiteName VARCHAR(1000),
		@SiteIds VARCHAR(250),
		@TeacherName VARCHAR(1000),
		@TeacherIds VARCHAR(250);

	SELECT	@Print = 'Roll up site names for children in multiple sites (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
	RAISERROR(@Print, 10, 1) WITH NOWAIT;
	UPDATE	#NameConcat
	SET	@SiteName = SiteName =
			CASE WHEN ClassScheduleId IS NULL AND ChildId = @PrevChildId THEN @SiteName + ', ' + SiteName
			ELSE SiteName END,
		@SiteIds = SiteIds =
			CASE WHEN ClassScheduleId IS NULL AND ChildId = @PrevChildId THEN @SiteIds + ',' + SiteIds
			ELSE SiteIds END,
		@TeacherName = TeacherName =
			CASE WHEN ClassScheduleId IS NOT NULL AND ChildId = @PrevChildId THEN @TeacherName + ', ' + TeacherName
			ELSE TeacherName END,
		@TeacherIds = TeacherIds =
			CASE WHEN ClassScheduleId IS NOT NULL AND ChildId = @PrevChildId THEN @TeacherIds + ',' + TeacherIds
			ELSE TeacherIds END,
		@PrevChildId = ChildId
	FROM	#NameConcat
	WHERE @SchoolYearId IS NULL OR SchoolYearId = @SchoolYearId
	OPTION (MAXDOP 1);

	SELECT	@Print = 'Move joined site names to main result set (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
	RAISERROR(@Print, 10, 1) WITH NOWAIT;
	WITH MAXIDS AS
		(
		SELECT	ChildId,
			MAX(AutoId) AS MaxAutoId
		FROM	#NameConcat
		WHERE @SchoolYearId IS NULL OR SchoolYearId = @SchoolYearId
		GROUP BY
			ChildId
		),
		NAMES AS
		(
		SELECT	DATA.ChildId,
			DATA.SiteIds,
			DATA.SiteName,
			DATA.ClassScheduleId,
			DATA.ClassName,
			DATA.TeacherIds,
			DATA.TeacherName,
			DATA.ClassStarted
		FROM	#NameConcat DATA
			INNER JOIN MAXIDS
				ON MAXIDS.ChildId = DATA.ChildId
				AND MAXIDS.MaxAutoId = DATA.AutoId
		)
	UPDATE	RSLT
	SET	SiteIds = NAMES.SiteIds,
		Site = NAMES.SiteName,
		ClassScheduleId = NAMES.ClassScheduleId,
		Class = NAMES.ClassName,
		TeacherIds = NAMES.TeacherIds,
		TeacherName = NAMES.TeacherName,
		ClassStarted = NAMES.ClassStarted
	FROM	#Results RSLT
		INNER JOIN NAMES
			ON NAMES.ChildId = RSLT.ChildId
	WHERE	RSLT.IsExpired = 0;

	--Note: we're sorting by site, class, or teacher, so we had to wait until we pulled the data for the entire state
	--before pulling out our page ...
	IF @SortExpression IN ('TeacherName', 'Class', 'Site')
	BEGIN
		UPDATE	#Results
		SET	SortKey = CASE @SortExpression
				WHEN 'TeacherName' THEN COALESCE(LEFT(TeacherName, 75), '') + CONVERT(VARCHAR, ChildId)
				WHEN 'Class' THEN COALESCE(LEFT(Class, 75), '') + CONVERT(VARCHAR, ChildId)
				WHEN 'Site' THEN COALESCE(LEFT(Site, 75), '') + CONVERT(VARCHAR, ChildId)
				ELSE SortKey
			END;
			
		CREATE INDEX #IX_Results_SortKey ON #Results (SortKey);
	
		DECLARE	@DeleteUntilRow INT = (@RowsPerPage * (@Page - 1));

		WITH CTE AS
			(
			SELECT	TOP (@DeleteUntilRow)
				*
			FROM	#Results RSLT
			ORDER BY
				CASE WHEN @SortDirection = 'Descending' THEN
					SortKey
				ELSE
					NULL
				END DESC,
				CASE WHEN @SortDirection = 'Ascending' THEN
					SortKey
				ELSE
					NULL
				END ASC
			)
		DELETE
		FROM	CTE;
	END;

	SELECT	@Print = 'Pulling PrescreenId (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
	RAISERROR(@Print, 10, 1) WITH NOWAIT;
	UPDATE	RSLT
	SET	PrescreenId = P.PrescreenId,
		PriorityPoints = PDATA.PriorityPoints
	FROM	#Results RSLT
		INNER JOIN Prescreen P
			ON P.ChildId = RSLT.ChildId
			AND P.IsDeleted = 0
		INNER JOIN PrescreenData PDATA
			ON PDATA.PrescreenId = P.PrescreenId
			AND PDATA.IsDeleted = 0;

	SELECT	@Print = 'Pulling ApplicationDataId (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
	RAISERROR(@Print, 10, 1) WITH NOWAIT;
	WITH CTE AS
		(
		SELECT	RSLT.ChildId,
			ADATA.ApplicationDataId,
			CASE
				WHEN ADATA.IsComplete = 1 OR ADATA.PriorityPoints > RSLT.PriorityPoints
					THEN ADATA.PriorityPoints
				ELSE
					RSLT.PriorityPoints 
			END AS PriorityPoints,
			ROW_NUMBER() OVER	(
								PARTITION BY RSLT.ChildId
								ORDER BY CASE
									WHEN ADATA.SchoolYearId = 4 AND ADATA.IsComplete = 1 THEN -1000000
									WHEN ADATA.IsComplete = 1 THEN ADATA.ApplicationDataId * -1
									WHEN ADATA.SchoolYearId = 4 THEN 0 
									ELSE ADATA.ApplicationDataId 
								END) AS AppDataRank
		FROM	#Results RSLT
			INNER JOIN Application APP
				ON APP.ChildId = RSLT.ChildId
				AND APP.IsDeleted = 0
			INNER JOIN ApplicationData ADATA
				ON ADATA.ApplicationId = APP.ApplicationId
				AND ADATA.IsDeleted = 0
		)
	UPDATE	RSLT
	SET	ApplicationDataId = CTE.ApplicationDataId,
		PriorityPoints = CTE.PriorityPoints
	FROM	#Results RSLT
		INNER JOIN CTE
			ON CTE.ChildId = RSLT.ChildId
			AND CTE.AppDataRank = 1;

	PRINT 'Updating children who are enrolled in another year to have Complete App across all years ...';
	UPDATE	RSLT
	SET	ChildStatusFlags = (ChildStatusFlags ^ @FLAG_NOAPPLICATION) | @FLAG_APPLICATION
	FROM	#Results RSLT
	WHERE	(
				ChildStatusFlags & @FLAG_NOAPPLICATION > 0
			AND	EXISTS	(
					SELECT	1
					FROM	vwChildProgramStatus WITH (NOEXPAND)
					WHERE	ChildId = RSLT.ChildId
					AND	SchoolYearId != @SchoolYearId
					AND	(
							Enrolled = 1
						OR	Exited = 1
						)
					)
			)
	OR	(
		EXISTS	(
				--If there is a future completed application, use that.
				SELECT	1
				FROM	ApplicationData
				WHERE	ApplicationDataId = RSLT.ApplicationDataId
				AND	IsComplete = 1
				AND	SchoolYearId > @SchoolYearId
				)
		);

	PRINT 'Updating children who were ever enrolled ...';
	UPDATE	RSLT
	SET	ChildStatusFlags = ChildStatusFlags | @FLAG_EVERENROLLED
	FROM	#Results RSLT
	WHERE	EXISTS	(
				SELECT	1
				FROM	vwChildProgramStatus WITH (NOEXPAND)
				WHERE	ChildId = RSLT.ChildId
				AND	(
						Enrolled = 1
					OR	Exited = 1
					)
				);

	SELECT	@Print = 'Pulling current status (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
	RAISERROR(@Print, 10, 1) WITH NOWAIT;
  	UPDATE	RSLT
	SET	ChildStatus = PROGSTATUS.Name,
		ChildStatusId = PROGSTATUS.ProgramStatusTypeId
	FROM	#Results RSLT
		INNER JOIN ProgramStatusType PROGSTATUS
			ON PROGSTATUS.IsDeleted = 0
			AND CASE
					WHEN RSLT.ChildStatusFlags & @FLAG_PENDINGTRANSFER > 0 THEN @PROGSTATUS_PENDINGTRANSFER
					WHEN RSLT.ChildStatusFlags & @FLAG_PENDINGEXIT > 0 THEN @PROGSTATUS_PENDINGEXIT
					WHEN RSLT.ChildStatusFlags & @FLAG_INCOMPLETEEXIT > 0 THEN @PROGSTATUS_INCOMPLETEEXIT
					WHEN RSLT.ChildStatusFlags & @FLAG_ENROLLED > 0 THEN @PROGSTATUS_ENROLLED
					WHEN RSLT.ChildStatusFlags & @FLAG_ONWAITLIST > 0 THEN @PROGSTATUS_ONWAITLIST
					WHEN RSLT.ChildStatusFlags & @FLAG_EXITED > 0 THEN @PROGSTATUS_EXITED
					WHEN RSLT.ChildStatusFlags & @FLAG_APPLICATION > 0 THEN @PROGSTATUS_APPLICATION
					WHEN RSLT.ChildStatusFlags & @FLAG_NOAPPLICATION > 0 THEN @PROGSTATUS_NOAPPLICATION
					WHEN RSLT.ChildStatusFlags & @FLAG_PRESCREEN > 0 THEN @PROGSTATUS_PRESCREEN
					WHEN RSLT.ChildStatusFlags & @FLAG_NOPRESCREEN > 0 THEN @PROGSTATUS_NOPRESCREEN
					ELSE NULL
				END = PROGSTATUS.ProgramStatusTypeId;

	SET ROWCOUNT @RowsPerPage;

	SELECT	@Print = 'Final results -> client (' + CONVERT(VARCHAR, GETDATE(), 109) + ')';
	RAISERROR(@Print, 10, 1) WITH NOWAIT;
	SELECT	*
	FROM	#Results
	ORDER BY
		CASE WHEN @SortDirection = 'Descending' THEN
			SortKey
		ELSE
			NULL
		END DESC,
		CASE WHEN @SortDirection = 'Ascending' THEN
			SortKey
		ELSE
			NULL
		END ASC;

	SET ROWCOUNT 0;

	DROP TABLE #Results;
	DROP TABLE #NameConcat;
	DROP TABLE #PreResults;
END;

Thanks & Regards,

Abdul Aleem Mohammad
St Louis MO - USA

PIEBALDconsult18-Feb-15 16:21

PIEBALDconsult

18-Feb-15 16:21

Do you really expect someone to read all that and understand it better than you do?

indian14319-Feb-15 6:26

19-Feb-15 6:26

Yes man I am sorry I got pissed off with guy who wrote without comments, but good thing is I brought all my peace back again and I read it and fixed the issue. Thanks for reminding me of my mistake or anger, after all we are all human beings.
Sorry once again.

Thanks & Regards,

Abdul Aleem Mohammad
St Louis MO - USA

Wendelius18-Feb-15 17:28

18-Feb-15 17:28

The code you have posted is far too big for someone to read and understand what you're trying t do in each section.

When you say that the procedure works fine if you call it using plain SQL and EXEC and it doesn't work when you call it from the application, the problem most likely resides in the application, not in the procedure.If the parameters are really the same in both cases the only thing that would change the procedure behaviour is changes in the database.

You should double check that the parameters leaving from your application are really the same. Use the debugger to check what happens in the calling code. If that seems valid then debug the procedure and see what happens. For more information see How to: Debug Stored Procedures[^]

indian14319-Feb-15 6:32

19-Feb-15 6:32

I got it fixed, just asking one question that if we have Select statement in between on Table variables would the Stored procedure not work properly, I had it some where and I removed it. It was working fine on the Query Window because it displays all the Selects but wasn't working from UI probably it first select as record set may be my guess. If you know more clarify it is just an understanding discussion but the problem is resolved.

Thanks for all your support, and the person who wrote wasn't having comments to understand it better.

Thanks & Regards,

Abdul Aleem Mohammad
St Louis MO - USA

Wendelius19-Feb-15 6:57

19-Feb-15 6:57

The BETWEEN condition needs two single values; the lower and the upper bound. For example:

SQL

WHERE NumColumn BETWEEN 5 AND 10

In that example you can't replace neither 5 or 10 with table variables since then there would be more than one value in the comparison. You can think the BETWEEN condition as two separate conditions, like:

SQL

WHERE NumColumn >= 5 
AND NumColumn <= 10

I hope this clarifies your question.

Mycroft Holmes18-Feb-15 23:54

Mycroft Holmes

18-Feb-15 23:54

In addition to what mika recommended you can use profiler to inspect precisely what is being sent from your ui.

Never underestimate the power of human stupidity
RAH

Eddy Vluggen19-Feb-15 5:31

Eddy Vluggen

19-Feb-15 5:31

I'd recommend to split that monster up into multiple sprocs.

What does "not working" mean? Does it throw an exception? If yes, what does it say? If the UI shows different results, you're either not calling the same sproc, or not using the same parameters.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

indian14319-Feb-15 6:26

Multiple Queries in one Stored procedure

19-Feb-15 6:26

Yeah fixed it in the same manner thank you.

Thanks & Regards,

Abdul Aleem Mohammad
St Louis MO - USA

sudevsu18-Feb-15 3:42

18-Feb-15 3:42

Hello everyone...

I have five tables say T1,T2,T3,T4,T5. Each table has different columns say like T1 has T1CL1,T1CL2,T1CL3
and Table T2 has T2CL1,T2CL2,T2CL3 and so on...
Now I want to Create a Stored procedure selecting only few columns from Each table and it should return everything in only one Dataset. Is it possible? If Yes... This is what I tried and didn't get anything I want.

SQL

Select T1CL1,T1CL2 from T1 where T1CL3 = 'Something'
Select T2CL1,T2CL3 from T2 where T1CL2 = 'prm1'
Select T3CL3,T3CL4 from T3 where T3CL2 = 'prm2'
Select T4CL1,T4CL3 from T4 where T4CL2 = 'prm3'
Select T5CL1,T5CL3 from T5 where T1CL2 = 'prm4'

But I don't know how save the data from each query and place everything from SP into a Dataset?
Can anyone help me with this Please

Your help is much appreciated.
Thanks
Happy Coding!

modified 18-Feb-15 10:01am.

PIEBALDconsult18-Feb-15 4:00

PIEBALDconsult

18-Feb-15 4:00

Perhaps you want a UNION?

sudevsu18-Feb-15 4:08

18-Feb-15 4:08

Correct something like that. I tried few in google but didn't find anything helpful. So I want to ask experts in here to find a solution.

Your help is much appreciated.
Thanks
Happy Coding!

sudevsu18-Feb-15 4:10

18-Feb-15 4:10

I also read something below

SQL

the number of columns appears in the corresponding SELECT statements must be equal.
The columns appear in  the corresponding positions of each SELECT statement must have the same data type or at least convertible data type.

I don't have same number of columns that must be selected. Neither of same datatype

Your help is much appreciated.
Thanks
Happy Coding!

Corporal Agarn18-Feb-15 4:33

Corporal Agarn

18-Feb-15 4:33

Put in nulls for the tables that do not have enough columns.
Change the data type by using CAST or CONVERT on the column within the select statement.

Mongo: Mongo only pawn... in game of life.

Wendelius18-Feb-15 4:37

18-Feb-15 4:37

Normally you would use union or union all. So in this case

SQL

Select T1CL1,T1CL2 from T1 where T1CL3 = 'Something'
union all
Select T2CL1,T2CL3 from T2 where T1CL2 = 'prm1'
union all
Select T3CL3,T3CL4 from T3 where T3CL2 = 'prm2'
union all
Select T4CL1,T4CL3 from T4 where T4CL2 = 'prm3'
union all
Select T5CL1,T5CL3 from T5 where T1CL2 = 'prm4'

However you said that you actually have different amount of columns and the data types are do not match or cannot be converted. If that really is the case, then you need to fetch multiple result sets from a single procedure.

Also if you can execute the statement as a batch from client side, then have a read at Executing multiple SQL statements as one against SQL Server[^]

sudevsu18-Feb-15 5:11

18-Feb-15 5:11

Can I use Join if there is a Table T6 which has all the primary keys of T1,T2,T3,T4 and T5?

Your help is much appreciated.
Thanks
Happy Coding!

Wendelius18-Feb-15 6:41

18-Feb-15 6:41

You can use join in a normal way on each select

Richard Deeming18-Feb-15 5:43

Richard Deeming

18-Feb-15 5:43

You don't need to enable MARS to fetch multiple result-sets from a single query; you just load each result-set sequentially. A SqlDataAdapter will take care of that for you, or you can call IDataReader.NextResult in a loop, loading each result-set as you go.

You only need MARS if you're going to have multiple active data-readers open on the same connection at the same time.

In other words:

// This needs MARS:
using (var connection = new SqlConnection("..."))
using (var outerCommand = new SqlCommand("...", connection))
{
    connection.Open();
    using (IDataReader outerReader = outerCommand.ExecuteReader())
    {
        while (outerReader.Read())
        {
            using (var innerCommand = new SqlCommand("...", connection))
            {
                using (IDataReader innerReader = innerCommand.ExecuteReader())
                {
                    while (innerReader.Read())
                    {
                        ...
                    }
                }
            }
        }
    }
}

// This doesn't:
using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand("SELECT ...; SELECT ...; SELECT ...;", connection))
{
    connection.Open();
    using (IDataReader reader = command.ExecuteReader())
    {
        do
        {
            while (reader.Read())
            {
                ...
            }
        } 
        while (reader.NextResult())
    }
}

// Neither does this:
var dataSet = new DataSet();

using (var connection = new SqlConnection("..."))
using (var command = new SqlCommand("SELECT ...; SELECT ...; SELECT ...;", connection))
{
    var adapter = new SqlDataAdapter(command);
    adapter.Fill(dataSet);
}

"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer

sudevsu18-Feb-15 6:21

18-Feb-15 6:21

Thank you Rich

Your help is much appreciated.
Thanks
Happy Coding!

Wendelius18-Feb-15 6:42

18-Feb-15 6:42

That's true. Don't know what I was thinking... Thanks for pointing that out.