How to short circuit if value passed in is null, do something if is not null

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'unsp_ApprenticeAdvancementReportLoad')
	DROP PROCEDURE foo.unsp_ApprenticeAdvancementReportLoad
GO

CREATE PROCEDURE foo.unsp_ApprenticeAdvancementReportLoad
(
	@LocNums nvarchar(2000) = null,
	@DateBegin datetime = null,
	@DateEnd datetime = null,
	@SortBy nvarchar(50) = null
)
AS

SELECT DISTINCT mainAppr.LastName
	,mainAppr.FirstName
	,mainAppr.UBCID UBCID	
	,lu.LocalNumber LocalNumber
	,lu.ID LocalUnionID
	,ap.Period
	,ap.PercentOfJourneyWages AdvancementPer	
	,aah.dayAdvancementDate AdvancementDate		
FROM foo.Apprentices mainAppr
	LEFT JOIN foo.ApprenticeAdvancementHistory aah ON aah.ID = mainAppr.CurrentAdvancementID   
	LEFT JOIN foo.ApprenticePeriods ap on aah.ApprenticePeriodID = ap.ID
	LEFT JOIN foo.LocalUnions lu ON lu.ID = mainAppr.LocalUnionID 
	LEFT JOIN foo.ApprenticeStatus apprStatus ON mainAppr.ApprenticeTypeID = apprStatus.ApprenticeTypeID   
WHERE  apprStatus.IsInProgram = 1 
AND ((@LocNums is null) OR lu.LocalNumber IN (UNAdmin.unf_SplitToString(@LocNums, ',')))
AND ((@DateBegin is null) OR (AdvancementDate >= @DateBegin))
AND @DateEnd is null OR AdvancementDate <= @DateEnd
ORDER BY ((@sortby is null) OR @SortBy)

Open in new window



This part:

AND ((@LocNums is null) OR lu.LocalNumber IN (UNAdmin.unf_SplitToString(@LocNums, ',')))
AND ((@DateBegin is null) OR (AdvancementDate >= @DateBegin))
AND @DateEnd is null OR AdvancementDate <= @DateEnd
ORDER BY ((@sortby is null) OR @SortBy)

Open in new window


Keeps saying:

Msg 156, Level 15, State 1, Procedure unsp_ApprenticeAdvancementReportLoad, Line 28
Incorrect syntax near the keyword 'is'.

line 28:

AND ((@DateBegin is null) OR (AdvancementDate >= @DateBegin))


Is my TSQL valid and am I doing this correctly or is there a better way.

[[SQL Server 2008, btw.]]
LVL 5
Tom KnowltonWeb developerAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
PortletPaulConnect With a Mentor Commented:
The problem is the ORDER BY clause, not the WHERE clause.

try this please:

ORDER BY ISNULL(@SortBy,'')

{+edit} there is a set of parentheses missing in the where clause

            AND (@DateEnd IS NULL
                  OR AdvancementDate <= @DateEnd)


IF EXISTS (
            SELECT
                  *
            FROM sysobjects
            WHERE type = 'P'
                  AND name = 'unsp_ApprenticeAdvancementReportLoad'
      )
      DROP PROCEDURE foo.unsp_ApprenticeAdvancementReportLoad
GO

CREATE PROCEDURE foo.unsp_ApprenticeAdvancementReportLoad (@LocNums nvarchar(2000) = NULL,
@DateBegin datetime = NULL,
@DateEnd datetime = NULL,
@SortBy nvarchar(50) = NULL)
AS

      SELECT DISTINCT
            MAINAPPR.LastName
          , MAINAPPR.FirstName
          , MAINAPPR.UBCID UBCID
          , LU.LocalNumber LOCALNUMBER
          , LU.ID LOCALUNIONID
          , AP.Period
          , AP.PercentOfJourneyWages ADVANCEMENTPER
          , AAH.dayAdvancementDate ADVANCEMENTDATE
      FROM foo.Apprentices MAINAPPR
            LEFT JOIN foo.ApprenticeAdvancementHistory AAH ON AAH.ID = MAINAPPR.CurrentAdvancementID
            LEFT JOIN foo.ApprenticePeriods AP ON AAH.ApprenticePeriodID = AP.ID
            LEFT JOIN foo.LocalUnions LU ON LU.ID = MAINAPPR.LocalUnionID
            LEFT JOIN foo.ApprenticeStatus APPRSTATUS ON MAINAPPR.ApprenticeTypeID = APPRSTATUS.ApprenticeTypeID
      WHERE APPRSTATUS.IsInProgram = 1
            AND ((@LocNums IS NULL)
                  OR LU.LocalNumber IN (UNAdmin.unf_SplitToString(@LocNums, ',')))
            AND ((@DateBegin IS NULL)
                  OR (AdvancementDate >= @DateBegin))
            AND (@DateEnd IS NULL
                  OR AdvancementDate <= @DateEnd)
ORDER BY ISNULL(@SortBy,'')

Open in new window

0
 
Tom KnowltonWeb developerAuthor Commented:
That was it!!
0
All Courses

From novice to tech pro — start learning today.