Input Parameters for a Stored Procedures used by Forms

[Zack Godula]

I have the following stored procedure which features input parameters that
are retrieved from a form and then the resulting records are displayed on a
report:

ALTER PROCEDURE quality_user.SP_NCR_Details
(@StartDate datetime,
@EndDate datetime,
@Inspector varchar (50) = NULL)
AS
IF @Inspector IS NULL -- return all records
SELECT NCR, Issued_Date, Qty_Rejected, Disposition, Item, Item_Desc,
Project, Task, NC_Type, RootCause, System_Element, Location, Discrete_Job,
App_Code, PO_Number, PO_Line, Supplier_Number,
Where_Rejected, Oper_rej, Where_Occurred, NC_Source, ORG_BusinessUnit,
ORG_FunctionalArea, Supplier_Site, Inspector, Stock,
NCR_Close_Date
FROM dbo.V_NCR_Details
WHERE (Issued_Date BETWEEN @StartDate AND @EndDate)
ORDER BY NCR
ELSE -- return selected record
SELECT NCR, Issued_Date, Qty_Rejected, Disposition, Item, Item_Desc,
Project, Task, NC_Type, RootCause, System_Element, Location, Discrete_Job,
App_Code, PO_Number, PO_Line, Supplier_Number,
Where_Rejected, Oper_rej, Where_Occurred, NC_Source, ORG_BusinessUnit,
ORG_FunctionalArea, Supplier_Site, Inspector, Stock,
NCR_Close_Date
FROM dbo.V_NCR_Details
WHERE (Issued_Date BETWEEN @StartDate AND @EndDate) AND (Inspector =
@Inspector)
ORDER BY NCR

Currently I have three input parameters, but there are 8 combo boxes on my
form. The combo boxes will not always have values in them, except for the
start and end date combos. I would like to set up the input parameters so
that if no value is selected in the combo box, the stored procedures will
show all records for that field. The other combo boxes that I have include:
NC_Source, ORG_BusinessUnit, ORG_Functional Area, Project and Task.

Thank you in advance and let me know if you require any further information.

 

[Brendan Reynolds]

Here's an example from one of my own apps that does something similar. The
general idea is that if the user makes no selection the value of the
parameter is NULL, so I test for that and assign appropriate values to the
local variables accordingly. The local variables are then used in the WHERE
clause rather than using the parameters directly.

CREATE PROCEDURE dbo.Report_AddressList(

@ClassID int = NULL,
@MinDateOfBirth datetime = NULL,
@MaxDateOfBirth datetime = NULL,
@GenderID int = NULL,
@LevelID int = NULL,

) AS

DECLARE @SchoolID int
DECLARE @MinClassID int
DECLARE @MaxClassID int
DECLARE @MinGenderID int
DECLARE @MaxGenderID int
DECLARE @MinLevelID int
DECLARE @MaxLevelID int

SET @SchoolID = dbo.CurrentSchool()

IF @ClassID IS NULL
BEGIN
SET @MinClassID = (SELECT Min(ClassID) FROM dbo.Class INNER JOIN
dbo.Teacher ON dbo.Class.TeacherID = dbo.Teacher.TeacherID WHERE
dbo.Teacher.SchoolID = @SchoolID)
SET @MaxClassID = (SELECT Max(ClassID) FROM dbo.Class INNER JOIN
dbo.Teacher ON dbo.Class.TeacherID = dbo.Teacher.TeacherID WHERE
dbo.Teacher.SchoolID = @SchoolID)
END
ELSE
BEGIN
SET @MinClassID = @ClassID
SET @MaxClassID = @ClassID
END

IF @MinDateOfBirth IS NULL
SET @MinDateOfBirth = CAST('1753-1-1' as datetime)
IF @MaxDateOfBirth IS NULL
SET @MaxDateOfBirth = CAST('9999-12-30' as datetime)

IF @GenderID IS NULL
BEGIN
SET @MinGenderID = (SELECT Min(GenderID) FROM dbo.Gender)
SET @MaxGenderID = (SELECT Max(GenderID) FROM dbo.Gender)
END
ELSE
BEGIN
SET @MinGenderID = @GenderID
SET @MaxGenderID = @GenderID
END

IF @LevelID IS NULL
BEGIN
SET @MinLevelID = (SELECT Min(LevelID) FROM dbo.Level)
SET @MaxLevelID = (SELECT Max(LevelID) FROM dbo.Level)
END
ELSE
BEGIN
SET @MinLevelID = @LevelID
SET @MaxLevelID = @LevelID
END

SELECT dbo.Student.FamilyName + N', ' + dbo.Student.GivenName AS
StudentName, dbo.Address.Address1 + N', ' + dbo.Address.Address2 AS Address,
dbo.Address.PhoneNumber AS HomePhone,
dbo.Father.MobilePhone AS FatherMobile, dbo.Mother.MobilePhone AS
MotherMobile, dbo.Student.DateOfBirth

FROM dbo.Student LEFT OUTER JOIN
dbo.Class ON dbo.Student.ClassID = dbo.Class.ClassID
LEFT OUTER JOIN
dbo.Address ON dbo.Student.AddressID =
dbo.Address.AddressID LEFT OUTER JOIN
dbo.Mother ON dbo.Student.StudentID =
dbo.Mother.StudentID LEFT OUTER JOIN
dbo.Father ON dbo.Student.StudentID =
dbo.Father.StudentID

WHERE (dbo.Student.SchoolID = @SchoolID) AND
(dbo.Student.ClassID >= @MinClassID AND dbo.Student.ClassID <= @MaxClassID)
AND
(dbo.Student.DateOfBirth >= @MinDateOfBirth AND dbo.Student.DateOfBirth <=
@MaxDateOfBirth) AND
(dbo.Student.GenderID >= @MinGenderID AND dbo.Student.GenderID <=
@MaxGenderID) AND
(dbo.Class.LevelID >= @MinLevelID AND dbo.Class.LevelID <= @MaxLevelID)

 

[Zack Godula]

Brendan, Thank you for your input, I have modified your code and created a
new store procedure, but I am still getting errors. It will not let me run
the stored procedures because it says that there are errors near the "END"
keywords. Any ideas? Thank you, here is the code:

CREATE PROCEDURE quality_user.SP_NCR_Test
(@StartDate datetime= NULL,
@EndDate datetime = NULL,
@Inspector varchar = NULL,
@BusinessUnit varchar = NULL)

AS

DECLARE @MinInspector int
DECLARE @MaxInspector int
DECLARE @MinBusinessUnit int
DECLARE @MaxBusinessUnit int

IF @Inspector IS NULL
BEGIN
SET @MinInspector = (SELECT Min(Inspector) FROM dbo.V_NCR_Details.Inspector)
SET @MaxInspector = (SELECT Max(Inspector) FROM dbo.V_NCR_Details.Inspector)
END
ELSE
BEGIN
SET @MinInspector = @Inspector
SET @MaxInspector = @Inspector
END

IF @BusinessUnit IS NULL
BEGIN
SET @MinBusinessUnit = (SELECT Min(BusinessUnit) FROM
dbo.V_NCR_Details.ORG_BusinessUnit)
SET @MaxBusinessUnit = (SELECT Max(BusinessUnit) FROM
dbo.V_NCR_Details.ORG_BusinessUnit)
END
ELSE
BEGIN
SET @MinBusinessUnit = @BusinessUnit
SET @MaxBusinessUnit = @BusinessUnit
END

SELECT NCR, Issued_Date, Qty_Rejected, Disposition, Item, Item_Desc,
Project, Task, NC_Type, RootCause, System_Element, Location, Discrete_Job,
App_Code, PO_Number, PO_Line, Supplier_Number,
Where_Rejected, Oper_rej, Where_Occurred, NC_Source, ORG_BusinessUnit,
ORG_FunctionalArea, Supplier_Site, Inspector, Stock,
NCR_Close_Date
FROM dbo.V_NCR_Details
WHERE (Issued_Date BETWEEN @StartDate AND @EndDate) AND
(dbo.V_NCR_Details.Inspector >= @MinInspector AND
dbo.V_NCR_Details.Inspector <= @MaxInspector)
AND
(dbo.V_NCR_Details.ORG_BusinessUnit >= @MinBusinessUnit AND
dbo.V_NCR_Details.ORG_BusinessUnit <= @MaxBusinessUnit)

ORDER BY NCR

 

[Brendan Reynolds]

The data type of your @Inspector and @BusinessUnit parameters is declared as
varchar, but the data type of your @MinInspector, @MaxInspector,
@MinBusinessUnit and @MaxBusinessUnit variables is declared as int.