The following stored proc is not working

indian143

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 @

PIEBALDconsult

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

Wendelius

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[^]

Mycroft Holmes

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

Lost User

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 :suss: If you can't read my code, try converting it here[^]

indian143

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

indian143

Yeah fixed it in the same manner thank you.

Thanks & Regards, Abdul Aleem Mohammad St Louis MO - USA

indian143

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

Wendelius

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

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:

WHERE NumColumn >= 5
AND NumColumn <= 10

I hope this clarifies your question.