sproc question

Question

sproc question

mjohnson 71479

SSC Eights!

Points: 846
More actions
July 18, 2018 at 10:18 am

#354516

Is there a better way for optional date parameters to be used inside of a stored procedure? I currently have a sproc that passes in 6 parameters to build my query. I want to add 2 more values that are nullable @StartDate to give the person the option of entering a date to grab records on or after that date. @EndDate to give the person the ability to search for records up to that date. if both are used then they would only return records between the dates. It is already quite long and the only way I know to do it would be to use if statements and have separate querys   for example
IF @StartDate != NULL AND @EndDate = NULL BEGIN         SELECT             ...         FROM             ...         WHERE             ...        AND JA.ApplyDate >= @StartDate                  END IF @StartDate = NULL AND @EndDate != NULL BEGIN         SELECT             ...         FROM             ...         WHERE             ...        AND JA.ApplyDate < DATEADD(d,1,@EndDate)                  END IF @StartDate != NULL AND @EndDate != NULL BEGIN         SELECT             ...         FROM             ...         WHERE             ...        AND JA.ApplyDate >= @StartDate             AND JA.ApplyDate < DATEADD(d,1,@EndDate) -- I did not use BETWEEN when a value is passed for both because the ApplyDate column is a datetime field     END IF @StartDate = NULL and @EndDate = NULL     BEGIN         -- use existing code     END
Here is my existing sproc. it is already quite long. I will explain the archive value for jobs. I pass it in as an int so that I can have 3 values but the column in the jobs table is a bit. (the third value, 2, is all records )
CREATE PROCEDURE [dbo].[ApplicantList]
@LocationID int, @FolderID int, @JobID int, @archive int, @AdminID nvarchar(15), @AppArchive tinyint, @StartDate date = NULL,   --- I want to ADD @EndDate date = NULL    --- I want to ADD
AS
SET NOCOUNT ON
DECLARE @RoleID int
SELECT     @RoleID = RoleID FROM     dbo.AdminUsers WHERE     AdminID = @AdminID
IF @archive = 2     BEGIN         IF @RoleID < 4             BEGIN                 SELECT DISTINCT                     JA.ApplicantID,                     JA.ApplicationID,                     JA.QQViewed,                     JA.AppViewed,                     JA.CVViewed,                     JA.SSViewed,                     JA.RptViewed,                     A.PrescreenScore,                     A.ApplyDate,                     A.GivenName,                     A.FamilyName,                     J.JobTitle,                     L.AreaName                 FROM                     dbo.Applicant JA INNER JOIN                     dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN                     dbo.Jobs J ON A.JobID = J.JobID INNER JOIN                     dbo.Locations L ON JA.LocationID = L.LocationID                 WHERE                     (JA.LocationID = @LocationID OR @LocationID = 9999)                     AND (JA.FolderID = @FolderID OR @FolderID = 99)                     AND (A.JobID = @JobID OR @JobID = 9999)                     AND (JA.Archived = @AppArchive OR @AppArchive = 2)             END         IF @RoleID = 4 -- Division Mgr             BEGIN                 SELECT DISTINCT                     JA.ApplicantID,                     JA.ApplicationID,                     JA.QQViewed,                     JA.AppViewed,                     JA.CVViewed,                     JA.SSViewed,                     JA.RptViewed,                     A.PrescreenScore,                     A.ApplyDate,                     A.GivenName,                     A.FamilyName,                     J.JobTitle,                     L.AreaName                 FROM                     dbo.Applicant JA INNER JOIN                     dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN                     dbo.Jobs J ON A.JobID = J.JobID INNER JOIN                     dbo.Locations L ON JA.LocationID = L.LocationID                 WHERE                     (JA.LocationID = @LocationID OR @LocationID = 9999)                     AND (JA.FolderID = @FolderID OR @FolderID = 99)                     AND (A.JobID = @JobID OR @JobID = 9999)                     AND J.DivisionID IN (SELECT DivisionID FROM dbo.DivisionManager WHERE AdminID = @AdminID)                     AND (JA.Archived = @AppArchive OR @AppArchive = 2)             END         IF @RoleID = 5 -- District Mgr             BEGIN                 SELECT DISTINCT                     JA.ApplicantID,                     JA.ApplicationID,                     JA.QQViewed,                     JA.AppViewed,                     JA.CVViewed,                     JA.SSViewed,                     JA.RptViewed,                     A.PrescreenScore,                     A.ApplyDate,                     A.GivenName,                     A.FamilyName,                     J.JobTitle,                     L.AreaName                 FROM                     dbo.Applicant JA INNER JOIN                     dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN                     dbo.Jobs J ON A.JobID = J.JobID INNER JOIN                     dbo.Locations L ON JA.LocationID = L.LocationID INNER JOIN                     dbo.Zone_Locations ZL ON ZL.LocationID = L.LocationID INNER JOIN                     dbo.Zones Z ON Z.ZoneID = ZL.ZoneID INNER JOIN                     dbo.Region_Zones RZ ON RZ.ZoneID = Z.ZoneID INNER JOIN                     dbo.Regions R on R.RegionID = RZ.RegionID INNER JOIN                     dbo.District_Regions DR ON DR.RegionID = R.RegionID                 WHERE                     (JA.LocationID = @LocationID OR @LocationID = 9999)                     AND (JA.FolderID = @FolderID OR @FolderID = 99)                     AND (A.JobID = @JobID OR @JobID = 9999)                     AND DR.DistrictID IN (SELECT DistrictID FROM dbo.DistrictManager WHERE AdminID = @AdminID)                     AND (JA.Archived = @AppArchive OR @AppArchive = 2)             END         IF @RoleID = 6 -- Region Mgr             BEGIN                 SELECT DISTINCT                     JA.ApplicantID,                     JA.ApplicationID,                     JA.QQViewed,                     JA.AppViewed,                     JA.CVViewed,                     JA.SSViewed,                     JA.RptViewed,                     A.PrescreenScore,                     A.ApplyDate,                     A.GivenName,                     A.FamilyName,                     J.JobTitle,                     L.AreaName                 FROM                     dbo.Applicant JA INNER JOIN                     dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN                     dbo.Jobs J ON A.JobID = J.JobID INNER JOIN                     dbo.Locations L ON JA.LocationID = L.LocationID INNER JOIN                     dbo.Zone_Locations ZL ON ZL.LocationID = L.LocationID INNER JOIN                     dbo.Zones Z ON Z.ZoneID = ZL.ZoneID INNER JOIN                     dbo.Region_Zones RZ ON RZ.ZoneID = Z.ZoneID                 WHERE                     (JA.LocationID = @LocationID OR @LocationID = 9999)                     AND (JA.FolderID = @FolderID OR @FolderID = 99)                     AND (A.JobID = @JobID OR @JobID = 9999)                     AND RZ.RegionID IN (SELECT RegionID FROM dbo.RegionManager WHERE AdminID = @AdminID)                     AND (JA.Archived = @AppArchive OR @AppArchive = 2)             END         IF @RoleID = 7 -- Zone Mgr             BEGIN                 SELECT DISTINCT                     JA.ApplicantID,                     JA.ApplicationID,                     JA.QQViewed,                     JA.AppViewed,                     JA.CVViewed,                     JA.SSViewed,                     JA.RptViewed,                     A.PrescreenScore,                     A.ApplyDate,                     A.GivenName,                     A.FamilyName,                     J.JobTitle,                     L.AreaName                 FROM                     dbo.Applicant JA INNER JOIN                     dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN                     dbo.Jobs J ON A.JobID = J.JobID INNER JOIN                     dbo.Locations L ON JA.LocationID = L.LocationID INNER JOIN                     dbo.Zone_Locations ZL ON ZL.LocationID = L.LocationID                 WHERE                     (JA.LocationID = @LocationID OR @LocationID = 9999)                     AND (JA.FolderID = @FolderID OR @FolderID = 99)                     AND (A.JobID = @JobID OR @JobID = 9999)                     AND ZL.ZoneID IN (SELECT ZoneID FROM dbo.ZoneManager WHERE AdminID = @AdminID)                     AND (JA.Archived = @AppArchive OR @AppArchive = 2)             END         IF @RoleID = 8 -- Department Mgr             BEGIN                 SELECT DISTINCT                     JA.ApplicantID,                     JA.ApplicationID,                     JA.QQViewed,                     JA.AppViewed,                     JA.CVViewed,                     JA.SSViewed,                     JA.RptViewed,                     A.PrescreenScore,                     A.ApplyDate,                     A.GivenName,                     A.FamilyName,                     J.JobTitle,                     L.AreaName                 FROM                     dbo.Applicant JA INNER JOIN                     dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN                     dbo.Jobs J ON A.JobID = J.JobID INNER JOIN                     dbo.Locations L ON JA.LocationID = L.LocationID                 WHERE                     (JA.LocationID = @LocationID OR @LocationID = 9999)                     AND (JA.FolderID = @FolderID OR @FolderID = 99)                     AND (A.JobID = @JobID OR @JobID = 9999)                     AND J.DepartmentID IN (SELECT DepartmentID FROM dbo.DepartmentManager WHERE AdminID = @AdminID)                     AND (JA.Archived = @AppArchive OR @AppArchive = 2)             END         IF @RoleID = 9 -- Dept Location Mgr             BEGIN                 SELECT DISTINCT                     JA.ApplicantID,                     JA.ApplicationID,                     JA.QQViewed,                     JA.AppViewed,                     JA.CVViewed,                     JA.SSViewed,                     JA.RptViewed,                     A.PrescreenScore,                     A.ApplyDate,                     A.GivenName,                     A.FamilyName,                     J.JobTitle,                     L.AreaName                 FROM                     dbo.Applicant JA INNER JOIN                     dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN                     dbo.Jobs J ON A.JobID = J.JobID INNER JOIN                     dbo.Locations L ON JA.LocationID = L.LocationID                 WHERE                     (JA.LocationID = @LocationID)                     AND (JA.FolderID = @FolderID OR @FolderID = 99)                     AND (A.JobID = @JobID OR @JobID = 9999)                     AND J.DepartmentID IN (SELECT DepartmentID FROM dbo.DepartmentManager WHERE AdminID = @AdminID)                     AND (JA.Archived = @AppArchive OR @AppArchive = 2)             END         IF @RoleID = 10 -- Location Mgr             BEGIN                 SELECT DISTINCT                     JA.ApplicantID,                     JA.ApplicationID,                     JA.QQViewed,                     JA.AppViewed,                     JA.CVViewed,                     JA.SSViewed,                     JA.RptViewed,                     A.PrescreenScore,                     A.ApplyDate,                     A.GivenName,                     A.FamilyName,                     J.JobTitle,                     L.AreaName                 FROM                     dbo.Applicant JA INNER JOIN                     dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN                     dbo.Jobs J ON A.JobID = J.JobID INNER JOIN                     dbo.Locations L ON JA.LocationID = L.LocationID                 WHERE                     (JA.LocationID = @LocationID)                     AND (JA.FolderID = @FolderID OR @FolderID = 99)                     AND (A.JobID = @JobID OR @JobID = 9999)             END     END ELSE     BEGIN         IF @RoleID < 4             BEGIN                 SELECT DISTINCT                     JA.ApplicantID,                     JA.ApplicationID,                     JA.QQViewed,                     JA.AppViewed,                     JA.CVViewed,                     JA.SSViewed,                     JA.RptViewed,                     A.PrescreenScore,                     A.ApplyDate,                     A.GivenName,                     A.FamilyName,                     J.JobTitle,                     L.AreaName                 FROM                     dbo.Applicant JA INNER JOIN                     dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN                     dbo.Jobs J ON A.JobID = J.JobID INNER JOIN                     dbo.Locations L ON JA.LocationID = L.LocationID                 WHERE                     (JA.LocationID = @LocationID OR @LocationID = 9999)                     AND (JA.FolderID = @FolderID OR @FolderID = 99)                     AND (A.JobID = @JobID OR (@JobID = 9999 AND J.Archived = CAST(@Archive AS bit)))                     AND (JA.Archived = @AppArchive OR @AppArchive = 2)             END         IF @RoleID = 4 -- Division Mgr             BEGIN                 SELECT DISTINCT                     JA.ApplicantID,                     JA.ApplicationID,                     JA.QQViewed,                     JA.AppViewed,                     JA.CVViewed,                     JA.SSViewed,                     JA.RptViewed,                     A.PrescreenScore,                     A.ApplyDate,                     A.GivenName,                     A.FamilyName,                     J.JobTitle,                     L.AreaName                 FROM                     dbo.Applicant JA INNER JOIN                     dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN                     dbo.Jobs J ON A.JobID = J.JobID INNER JOIN                     dbo.Locations L ON JA.LocationID = L.LocationID                 WHERE                     (JA.LocationID = @LocationID OR @LocationID = 9999)                     AND (JA.FolderID = @FolderID OR @FolderID = 99)                     AND (A.JobID = @JobID OR (@JobID = 9999 AND J.Archived = CAST(@Archive AS bit)))                     AND J.DivisionID IN (SELECT DivisionID FROM dbo.DivisionManager WHERE AdminID = @AdminID)                     AND (JA.Archived = @AppArchive OR @AppArchive = 2)             END         IF @RoleID = 5 -- District Mgr             BEGIN                 SELECT DISTINCT                     JA.ApplicantID,                     JA.ApplicationID,                     JA.QQViewed,                     JA.AppViewed,                     JA.CVViewed,                     JA.SSViewed,                     JA.RptViewed,                     A.PrescreenScore,                     A.ApplyDate,                     A.GivenName,                     A.FamilyName,                     J.JobTitle,                     L.AreaName                 FROM                     dbo.Applicant JA INNER JOIN                     dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN                     dbo.Jobs J ON A.JobID = J.JobID INNER JOIN                     dbo.Locations L ON JA.LocationID = L.LocationID INNER JOIN                     dbo.Zone_Locations ZL ON ZL.LocationID = L.LocationID INNER JOIN                     dbo.Zones Z ON Z.ZoneID = ZL.ZoneID INNER JOIN                     dbo.Region_Zones RZ ON RZ.ZoneID = Z.ZoneID INNER JOIN                     dbo.Regions R on R.RegionID = RZ.RegionID INNER JOIN                     dbo.District_Regions DR ON DR.RegionID = R.RegionID                 WHERE                     (JA.LocationID = @LocationID OR @LocationID = 9999)                     AND (JA.FolderID = @FolderID OR @FolderID = 99)                     AND (A.JobID = @JobID OR (@JobID = 9999 AND J.Archived = CAST(@Archive AS bit)))                     AND DR.DistrictID IN (SELECT DistrictID FROM dbo.DistrictManager WHERE AdminID = @AdminID)                     AND (JA.Archived = @AppArchive OR @AppArchive = 2)             END         IF @RoleID = 6 -- Region Mgr             BEGIN                 SELECT DISTINCT                     JA.ApplicantID,                     JA.ApplicationID,                     JA.QQViewed,                     JA.AppViewed,                     JA.CVViewed,                     JA.SSViewed,                     JA.RptViewed,                     A.PrescreenScore,                     A.ApplyDate,                     A.GivenName,                     A.FamilyName,                     J.JobTitle,                     L.AreaName                 FROM                     dbo.Applicant JA INNER JOIN                     dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN                     dbo.Jobs J ON A.JobID = J.JobID INNER JOIN                     dbo.Locations L ON JA.LocationID = L.LocationID INNER JOIN                     dbo.Zone_Locations ZL ON ZL.LocationID = L.LocationID INNER JOIN                     dbo.Zones Z ON Z.ZoneID = ZL.ZoneID INNER JOIN                     dbo.Region_Zones RZ ON RZ.ZoneID = Z.ZoneID                 WHERE                     (JA.LocationID = @LocationID OR @LocationID = 9999)                     AND (JA.FolderID = @FolderID OR @FolderID = 99)                     AND (A.JobID = @JobID OR (@JobID = 9999 AND J.Archived = CAST(@Archive AS bit)))                     AND RZ.RegionID IN (SELECT RegionID FROM dbo.RegionManager WHERE AdminID = @AdminID)                     AND (JA.Archived = @AppArchive OR @AppArchive = 2)             END         IF @RoleID = 7 -- Zone Mgr             BEGIN                 SELECT DISTINCT                     JA.ApplicantID,                     JA.ApplicationID,                     JA.QQViewed,                     JA.AppViewed,                     JA.CVViewed,                     JA.SSViewed,                     JA.RptViewed,                     A.PrescreenScore,                     A.ApplyDate,                     A.GivenName,                     A.FamilyName,                     J.JobTitle,                     L.AreaName                 FROM                     dbo.Applicant JA INNER JOIN                     dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN                     dbo.Jobs J ON A.JobID = J.JobID INNER JOIN                     dbo.Locations L ON JA.LocationID = L.LocationID INNER JOIN                     dbo.Zone_Locations ZL ON ZL.LocationID = L.LocationID                 WHERE                     (JA.LocationID = @LocationID OR @LocationID = 9999)                     AND (JA.FolderID = @FolderID OR @FolderID = 99)                     AND (A.JobID = @JobID OR (@JobID = 9999 AND J.Archived = CAST(@Archive AS bit)))                     AND ZL.ZoneID IN (SELECT ZoneID FROM dbo.ZoneManager WHERE AdminID = @AdminID)                     AND (JA.Archived = @AppArchive OR @AppArchive = 2)             END         IF @RoleID = 8 -- Department Mgr             BEGIN                 SELECT DISTINCT                     JA.ApplicantID,                     JA.ApplicationID,                     JA.QQViewed,                     JA.AppViewed,                     JA.CVViewed,                     JA.SSViewed,                     JA.RptViewed,                     A.PrescreenScore,                     A.ApplyDate,                     A.GivenName,                     A.FamilyName,                     J.JobTitle,                     L.AreaName                 FROM                     dbo.Applicant JA INNER JOIN                     dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN                     dbo.Jobs J ON A.JobID = J.JobID INNER JOIN                     dbo.Locations L ON JA.LocationID = L.LocationID                 WHERE                     (JA.LocationID = @LocationID OR @LocationID = 9999)                     AND (JA.FolderID = @FolderID OR @FolderID = 99)                     AND (A.JobID = @JobID OR (@JobID = 9999 AND J.Archived = CAST(@Archive AS bit)))                     AND J.DepartmentID IN (SELECT DepartmentID FROM dbo.DepartmentManager WHERE AdminID = @AdminID)                     AND (JA.Archived = @AppArchive OR @AppArchive = 2)             END         IF @RoleID = 9 -- Dept Location Mgr             BEGIN                 SELECT DISTINCT                     JA.ApplicantID,                     JA.ApplicationID,                     JA.QQViewed,                     JA.AppViewed,                     JA.CVViewed,                     JA.SSViewed,                     JA.RptViewed,                     A.PrescreenScore,                     A.ApplyDate,                     A.GivenName,                     A.FamilyName,                     J.JobTitle,                     L.AreaName                 FROM                     dbo.Applicant JA INNER JOIN                     dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN                     dbo.Jobs J ON A.JobID = J.JobID INNER JOIN                     dbo.Locations L ON JA.LocationID = L.LocationID                 WHERE                     (JA.LocationID = @LocationID)                     AND (JA.FolderID = @FolderID OR @FolderID = 99)                     AND (A.JobID = @JobID OR (@JobID = 9999 AND J.Archived = CAST(@Archive AS bit)))                     AND J.DepartmentID IN (SELECT DepartmentID FROM dbo.DepartmentManager WHERE AdminID = @AdminID)                     AND (JA.Archived = @AppArchive OR @AppArchive = 2)             END         IF @RoleID = 10 -- Location Mgr             BEGIN                 SELECT DISTINCT                     JA.ApplicantID,                     JA.ApplicationID,                     JA.QQViewed,                     JA.AppViewed,                     JA.CVViewed,                     JA.SSViewed,                     JA.RptViewed,                     A.PrescreenScore,                     A.ApplyDate,                     A.GivenName,                     A.FamilyName,                     J.JobTitle,                     L.AreaName                 FROM                     dbo.Applicant JA INNER JOIN                     dbo.Application A ON A.ApplicationID = JA.ApplicationID INNER JOIN                     dbo.Jobs J ON A.JobID = J.JobID INNER JOIN                     dbo.Locations L ON JA.LocationID = L.LocationID                 WHERE                     (JA.LocationID = @LocationID)                     AND (JA.FolderID = @FolderID OR @FolderID = 99)                     AND (A.JobID = @JobID OR (@JobID = 9999 AND J.Archived = CAST(@Archive AS bit)))                     AND (JA.Archived = @AppArchive OR @AppArchive = 2)             END     END

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 1

Yes there is. Done correctly, there's no chance of SQL Injection, either. Please see the article at the following link, which I consider to be the definitive article on the subject and have used the technique many times with great success.
https://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mjohnson 71479 SSC Eights! Points: 846 More actions · Answer 2

Thank you! I have rewritten it and it is working as intended.

Jeff Moden SSC Guru Points: 1003894 More actions · Answer 3

mjohnson 71479 - Wednesday, July 18, 2018 1:03 PM
Thank you! I have rewritten it and it is working as intended.

Very cool. Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)