SQL Query / Stroe Procedure Help

Hogie · April 16, 2018, 8:44pm

I Have Store procedure with the parameters listed below, The user interface consist of 4 Textboxs, for ACCOUNT_MASK , YEAR, START and END DATE and 3 check boxes.
the user can select any combination to search.

I am looking for any help on how to re write this query where I an not using Dynamic SQL if possible, ande I do not have all th IF / Else statements, and repeating Select statements for each block of the IF / Else statements.
The main problem is the Status_Code for each possible combination of the check boxes.. IF the user do not select any check box then it should return status_Code IN (4,5,6,7,8). Any help would be graetly appreciated.

@userID
@year
@Req
@Future_Req
@Future_PO
@Acct_Mask
@Start_Date
@End_Date

SELECT * From Table

IF @StartDate = '' And @EndDate = '' AND @Acct_Mask = ''
Begin
IF Req = 1 AND @Future_Req ='' AND @Future_PO = ''
WHERE
UserID=@UserID
AND status_Code IN (2,4,5,6,7,8)
AND year = @year
AND Cancelled = 0
AND close_open = 0

SELECT * From Table

ELSE IF Req = '' AND @Future_Req = 2 AND @Future_PO = ''
WHERE
UserID=@UserID
AND status_Code IN (4,5,6,7,8,94)
AND year = @year
AND Cancelled = 0
AND close_open = 0

ELSE IF Req = '' AND @Future_Req ='' AND @Future_PO = 3
WHERE
UserID=@UserID
AND status_Code IN (4,5,6,7,8,95,98)
AND year = @year
AND Cancelled = 0
AND close_open = 0

ELSE IF Req = 1 AND @Future_Req = 2 AND @Future_PO = ''
WHERE
UserID=@UserID
AND status_Code IN (2,4,5,6,7,8,94)
AND year = @year
AND Cancelled = 0
AND close_open = 0

ELSE IF Req = 1 AND @Future_Req ='' AND @Future_PO = 3
WHERE
UserID=@UserID
AND status_Code IN (2,4,5,6,7,8,95,98)
AND year = @year
AND Cancelled = 0
AND close_open = 0

ELSE IF Req = '' AND @Future_Req = 2 AND @Future_PO = 3
WHERE
UserID=@UserID
AND status_Code IN (4,5,6,7,8,94,95,98)
AND year = @year
AND Cancelled = 0
AND close_open = 0

ELSE IF Req = 1 AND @Future_Req = 2 AND @Future_PO = 3
WHERE
UserID=@UserID
AND status_Code IN (2,4,5,6,7,8,94,95,98)
AND year = @year
AND Cancelled = 0
AND close_open = 0

ELSE
WHERE
UserID=@UserID
AND status_Code IN (4,5,6,7,8)
AND year = @year
AND Cancelled = 0
AND close_open = 0

IF @StartDate <> '' OR @EndDate <> ''
Begin
IF Req = 1 AND @Future_Req ='' AND @Future_PO = ''
WHERE
UserID=@UserID
AND status_Code IN (2,4,5,6,7,8)
AND Pom.PODate >= @StartDate AND Pom.PODate <= @EndDate
AND Cancelled = 0
AND close_open = 0

ELSE IF Req = '' AND @Future_Req = 2 AND @Future_PO = ''
WHERE
UserID=@UserID
AND status_Code IN (4,5,6,7,8,94)
AND Pom.PODate >= @StartDate AND Pom.PODate <= @EndDate
AND Cancelled = 0
AND close_open = 0

ELSE IF Req = '' AND @Future_Req ='' AND @Future_PO = 3
WHERE
UserID=@UserID
AND status_Code IN (4,5,6,7,8,95,98)
AND Pom.PODate >= @StartDate AND Pom.PODate <= @EndDate
AND Cancelled = 0
AND close_open = 0

ELSE IF Req = 1 AND @Future_Req = 2 AND @Future_PO = ''
WHERE
UserID=@UserID
AND status_Code IN (2,4,5,6,7,8,94)
AND Pom.PODate >= @StartDate AND Pom.PODate <= @EndDate
AND Cancelled = 0
AND close_open = 0

ELSE IF Req = 1 AND @Future_Req ='' AND @Future_PO = 3
WHERE
UserID=@UserID
AND status_Code IN (2,4,5,6,7,8,95,98)
AND Pom.PODate >= @StartDate AND Pom.PODate <= @EndDate
AND Cancelled = 0
AND close_open = 0

ELSE IF Req = '' AND @Future_Req = 2 AND @Future_PO = 3
WHERE
UserID=@UserID
AND status_Code IN (4,5,6,7,8,94,95,98)
AND Pom.PODate >= @StartDate AND Pom.PODate <= @EndDate
AND Cancelled = 0
AND close_open = 0

ELSE IF Req = 1 AND @Future_Req = 2 AND @Future_PO = 3
WHERE
UserID=@UserID
AND status_Code IN (2,4,5,6,7,8,94,95,98)
AND Pom.PODate >= @StartDate AND Pom.PODate <= @EndDate
AND Cancelled = 0
AND close_open = 0

ELSE
WHERE
UserID=@UserID
AND status_Code IN (4,5,6,7,8)
AND Pom.PODate >= @StartDate AND Pom.PODate <= @EndDate
AND Cancelled = 0
AND close_open = 0

IF @Acct_Mask <> '' AND @StartDate = '' AND @EndDate = ''

IF Req = 1 AND  @Future_Req ='' AND @Future_PO = ''
	WHERE 
	UserID=@UserID 
	AND	status_Code IN (2,4,5,6,7,8) 
	AND year = @year
	AND Cancelled = 0
	AND close_open = 0
	AND (Poa.PxEaDisplay LIKE @Acct_Mask)

ELSE IF Req = '' AND @Future_Req = 2 AND @Future_PO = ''
WHERE
UserID=@UserID
AND status_Code IN (4,5,6,7,8,94)
AND year = @year
AND Cancelled = 0
AND close_open = 0
AND (Poa.PxEaDisplay LIKE @Acct_Mask)

ELSE IF Req = '' AND @Future_Req ='' AND @Future_PO = 3
WHERE
UserID=@UserID
AND status_Code IN (4,5,6,7,8,95,98)
AND year = @year
AND Cancelled = 0
AND close_open = 0
AND (Poa.PxEaDisplay LIKE @Acct_Mask)

ELSE IF Req = 1 AND @Future_Req = 2 AND @Future_PO = ''
WHERE
UserID=@UserID
AND status_Code IN (2,4,5,6,7,8,94)
AND year = @year
AND Cancelled = 0
AND close_open = 0
AND (Poa.PxEaDisplay LIKE @Acct_Mask)

ELSE IF Req = 1 AND @Future_Req ='' AND @Future_PO = 3
WHERE
UserID=@UserID
AND status_Code IN (2,4,5,6,7,8,95,98)
AND year = @year
AND Cancelled = 0
AND close_open = 0
AND (Poa.PxEaDisplay LIKE @Acct_Mask)

ELSE IF Req = '' AND @Future_Req = 2 AND @Future_PO = 3
WHERE
UserID=@UserID
AND status_Code IN (4,5,6,7,8,94,95,98)
AND year = @year
AND Cancelled = 0
AND close_open = 0
AND (Poa.PxEaDisplay LIKE @Acct_Mask)

ELSE IF Req = 1 AND @Future_Req = 2 AND @Future_PO = 3
WHERE
UserID=@UserID
AND status_Code IN (2,4,5,6,7,8,94,95,98)
AND year = @year
AND Cancelled = 0
AND close_open = 0
AND (Poa.PxEaDisplay LIKE @Acct_Mask)

ELSE
WHERE
UserID=@UserID
AND status_Code IN (4,5,6,7,8)
AND year = @year
AND Cancelled = 0
AND close_open = 0
AND (Poa.PxEaDisplay LIKE @Acct_Mask)

IF @ Acct_Mask <> '' AND @StartDate <> '' AND @EndDate <> ''
Begin

IF Req = 1 AND  @Future_Req ='' AND @Future_PO = ''
	WHERE 
	UserID=@UserID 
	AND	status_Code IN (2,4,5,6,7,8) 
	AND Pom.PODate >= @StartDate AND Pom.PODate <= @EndDate
	AND Cancelled = 0
	AND close_open = 0
	AND (Poa.PxEaDisplay LIKE @Acct_Mask)

ELSE IF Req = '' AND @Future_Req = 2 AND @Future_PO = ''
WHERE
UserID=@UserID
AND status_Code IN (4,5,6,7,8,94)
AND Pom.PODate >= @StartDate AND Pom.PODate <= @EndDate
AND Cancelled = 0
AND close_open = 0
AND (Poa.PxEaDisplay LIKE @Acct_Mask)

ELSE IF Req = '' AND @Future_Req ='' AND @Future_PO = 3
WHERE
UserID=@UserID
AND status_Code IN (4,5,6,7,8,95,98)
AND Pom.PODate >= @StartDate AND Pom.PODate <= @EndDate
AND Cancelled = 0
AND close_open = 0
AND (Poa.PxEaDisplay LIKE @Acct_Mask)

ELSE IF Req = 1 AND @Future_Req = 2 AND @Future_PO = ''
WHERE
UserID=@UserID
AND status_Code IN (2,4,5,6,7,8,94)
AND Pom.PODate >= @StartDate AND Pom.PODate <= @EndDate
AND Cancelled = 0
AND close_open = 0
AND (Poa.PxEaDisplay LIKE @Acct_Mask)

ELSE IF Req = 1 AND @Future_Req ='' AND @Future_PO = 3
WHERE
UserID=@UserID
AND status_Code IN (2,4,5,6,7,8,95,98)
AND Pom.PODate >= @StartDate AND Pom.PODate <= @EndDate
AND Cancelled = 0
AND close_open = 0
AND (Poa.PxEaDisplay LIKE @Acct_Mask)

ELSE IF Req = '' AND @Future_Req = 2 AND @Future_PO = 3
WHERE
UserID=@UserID
AND status_Code IN (4,5,6,7,8,94,95,98)
AND Pom.PODate >= @StartDate AND Pom.PODate <= @EndDate
AND Cancelled = 0
AND close_open = 0
AND (Poa.PxEaDisplay LIKE @Acct_Mask)

ELSE IF Req = 1 AND @Future_Req = 2 AND @Future_PO = 3
WHERE
UserID=@UserID
AND status_Code IN (2,4,5,6,7,8,94,95,98)
AND Pom.PODate >= @StartDate AND Pom.PODate <= @EndDate
AND Cancelled = 0
AND close_open = 0
AND (Poa.PxEaDisplay LIKE @Acct_Mask)

ELSE
WHERE
UserID=@UserID
AND status_Code IN (4,5,6,7,8)
AND Pom.PODate >= @StartDate AND Pom.PODate <= @EndDate
AND Cancelled = 0
AND close_open = 0
AND (Poa.PxEaDisplay LIKE @Acct_Mask)

gbritton · April 17, 2018, 1:23pm

Something's missing. e.g. above I see

ELSE IF Req = '' AND @Future_Req = 2 AND @Future_PO = ''
WHERE
UserID=@UserID
AND	status_Code IN (4,5,6,7,8,94)
AND year = @year
AND Cancelled = 0
AND close_open = 0

But you can't use WHERE with IF like that. it's invalid syntax

Also, I don't see any dynamic SQL.

Hogie · April 17, 2018, 3:16pm

Hi,
Sorry for the confusion, I am trying to implement this in static SQL, NOT Dynamic SQL, the above are examples of the many IF / ELSE conditions that is needed for all the possible combinations of the check boxes that the user can select. IF user select the Req checkbox. then 1 is pass to the @Req. parameter, and 2 for the @Future_Req and 3 for @Future_PO ... Each of the IF / ELSE branches has different Status_Code output requirement.

in the section below, the WHERE is just an example of what the WHERE should look like for that branch of the IF / ELSE .. ( Those WHERE are just examples of what the WHERE clause should be for each branch of the IF / ELSE, they are not actually been used together..

In the below code example, the user only select the Future_Req check box and the others are blank by default therefore we need all records with status_Code IN (4,5,6,7,8,94).

Sorry for the confusion, hope that explain a little clearer ... Thank you.

ELSE IF Req = '' AND @Future_Req = 2 AND @Future_PO = ''
WHERE
UserID=@UserID
AND status_Code IN (4,5,6,7,8,94)
AND year = @year
AND Cancelled = 0
AND close_open = 0

gbritton · April 17, 2018, 3:57pm

I think your proc might be simpler to write and maintain if you used dynamic sql. But that's just me. Otherwise, think about a table variable that has columns for the combinations plus an action column. Find the row in the table that matches the combo then perform the action. Call it a Command Pattern in SQL.