Post reply

sproc question

  • 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

  • July 18, 2018 at 11:16 am

    #1998052

    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)

  • July 18, 2018 at 1:03 pm

    #1998085

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

  • July 18, 2018 at 1:16 pm

    #1998090

    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)

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

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