asked on

SQL - handling a null and Procedure OPTIMIZE

I have a very complex stored proc...and if anyone wants to make this more simple..please advise.

The core issue though is I have a value in which a Null is being passed into this stored proc...

I need to handle this null...so it is allowed. In other words handle it as if the param was never passed to begin with.

The stored proc is long..so please condesne if you think its possible...otherwise just help with the null please.

The parameter that gets passed in as a nulll is @LSMnTaskStatusID.
If this happens I need this proc to act as if that never happened. I have no control over this parameter getting this passed but it will break the proc. Changing the param to a 0 or empty string will not work either...needs to be like it was never filtered.

USE table
GO
/****** Object:  StoredProcedure [dbo].[spPROC]    ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spPROC] 

@cSiteCode varchar(20)
,@source  varchar(max) = ''
,@txtTask varchar(max) = null
,@BGWBeginWith  bit =0
,@cbonClientID int = 0
,@cbonTaskTypeID varchar(max) =''
,@cbonTaskSubTypeID varchar(max) = ''
,@cbonTaskStatusID varchar(max) = ''
,@cbonPrimaryInternalContactID varchar(max) =''
,@MT1dDesiredDueDate datetime = '01/01/1900'
,@MT3dDesiredDueDate datetime = '12/31/9000'  
,@cbocSortOrder  varchar(max) = ''
,@tx2cTask varchar(max) = null
,@LSMnTaskPriorityID varchar(max) = ''
,@tx2cNote varchar(max) = ''
,@LSMnTaskTypeID varchar(max) = ''
,@LSMnTaskSubTypeID varchar(max) = ''
,@LSMnTaskStatusID varchar(max) = ''
,@LSMnClientID varchar(max) = ''
,@LSMcCampaign varchar(max) = ''
,@LSMnPrimaryContactID varchar(max) = ''
,@LSMnOtherContactID varchar(max) = ''
,@LSMnPrimaryInternalContactID varchar(max) = ''
,@LSMnCreatedContactID varchar(max) = ''
,@LSMnClientType varchar(max) = ''
,@cbolCompleted int = 0
,@chkUnAssignedEntity bit = 0
,@chkUnAssignedPrimary bit = 0
,@MT1dCreated datetime = '01/01/1900'
,@MT3dCreated datetime = '12/31/9000'
,@MT1dModified datetime = '01/01/1900'
,@MT3dModified datetime = '12/31/9000'
,@MT1dStartDate datetime = '01/01/1900'
,@MT3dStartDate datetime = '12/31/9000'

as
declare @sql nvarchar(max)
declare @Result  table(
[nTaskID] int
      ,[cTask] varchar(max)
      ,[cSiteCode] varchar(max)
      ,[nTaskPriorityID] int
      ,[nTaskStatusID] int
      ,[nTaskTypeID] int
      ,[cNote] varchar(max)
   ,[dDesiredDueDate]  smalldatetime
      ,[lCompleted] bit
      ,[nModifiedContactID] int null
      ,[dModified]  datetime null
      ,[nCreatedContactID]  int 
      ,[dCreated]  datetime
      ,[dStartDate] datetime null
      ,[nAmount] int
      ,[nProbability] int
      ,[dClose] datetime null
      ,[cFileAreaSiteCode] varchar(max)
      ,[cCampaign] varchar(max)
      ,[lEmailInternalUsers] bit null
      ,[lEmailAllUsers] bit null
      ,[cOrder] varchar(max)
      ,[lPrivate] bit
      ,[nPrivateStatus] int
      ,[nTaskSubTypeID] int
     
    ,cTaskPriority varchar(max)
    , cTaskStatus varchar(max)
    , cTaskType varchar(max)
    ,  cTaskSubType  varchar(max)
    ,  cModifiedContactName varchar(max)
    ,  cCreatedContactName  varchar(max) 
    , nClientID varchar(max)
    ,  cClientName varchar(max)
    
    , cPrimaryContactName varchar(max)
     
    )
begin

 select @sql = N'select distinct  tt.[nTaskID]
      ,tt.[cTask]
      ,tt.[cSiteCode]
      ,tt.[nTaskPriorityID]
      ,tt.[nTaskStatusID]
      ,tt.[nTaskTypeID]
      ,convert(varchar(max),tt.[cNote]) [cNote]
   --tt.[cNote]
      ,tt.[dDesiredDueDate]
      ,tt.[lCompleted]
      ,tt.[nModifiedContactID]
      ,tt.[dModified]
      ,tt.[nCreatedContactID]
      ,tt.[dCreated]
      ,tt.[dStartDate]
      ,tt.[nAmount]
      ,tt.[nProbability]
      ,tt.[dClose]
      ,tt.[cFileAreaSiteCode]
      ,tt.[cCampaign]
      ,tt.[lEmailInternalUsers]
      ,tt.[lEmailAllUsers]
      ,tt.[cOrder]
      ,tt.[lPrivate]
      ,tt.[nPrivateStatus]
      ,tt.[nTaskSubTypeID]
      
    , isnull(ctp.cValue, '''') cTaskPriority
    , isnull(cts.cValue, '''') cTaskStatus
    , isnull(ctt.cValue, '''') cTaskType
    , isnull(cst.cValue, '''') cTaskSubType 
    , isnull((mc.cFirstName + '' '' + mc.cLastName), '''') cModifiedContactName
    , isnull((cc.cFirstName + '' '' + cc.cLastName), '''') cCreatedContactName  
    , isnull(txCli.nClientID, -1) nClientID
    , isnull(cli.cName, ''[Un-Assigned]'') cClientName
    --, isnull(txCon.nContactID, -1) nContactID
    , isnull(dbo.udfGetTaskContactList(tt.nTaskID,1), ''[Un-Assigned]'') cPrimaryContactName
    --, ISNULL(tt2.nWFTaxYear, 0) nWFTaxYear
    --, ISNULL(tt2.cWFPeriodEnd, '''') cWFPeriodEnd  
  from tTask tt
   left join tCodes ctp on tt.nTaskPriorityID = ctp.nID
   left join tCodes cts on tt.nTaskStatusID = cts.nID
   left join tCodes ctt on tt.nTaskTypeID = ctt.nID
   left join tCodes cst on tt.nTaskSubTypeID = cst.nID 
   left join tContact mc on tt.nModifiedContactID = mc.nID
   left join tContact cc on tt.nCreatedContactID = cc.nID
   left join tTaskXClient txCli on tt.nTaskID = txCli.nTaskID
   left join tClient cli on txCli.nClientID = cli.nID
   left join tTaskXContact txCon on tt.nTaskID = txCon.nTaskID 
   left join tTaskXContact txConPrimary on tt.nTaskID = txConPrimary.nTaskID and txConPrimary.lPrimary = 1
   left join tTaskXContact txConOther  on tt.nTaskID = txConOther.nTaskID and txConOther.lPrimary != 1
   left join (select txConPI.*  from tTaskXContact txConPI 
      inner join tTask tt2 on  txConPI.nTaskID = tt2.nTaskID 
      inner join tcontact cc2 on cc2.nid  = txConPI.nContactID and  txConPI.lPrimary = 1) txConPIlist   on tt.nTaskID = txConPIList.nTaskID
   left join tContact con on txCon.nContactID = con.nID
   --left join ext..tTask2 tt on tt.nTaskID = tt.nTaskID   
  where tt.cSitecode = ''' + @cSiteCode + ''''

  if @tx2cTask is not null  and @tx2cTask != ''
 begin
  if @BGWBeginWith =1
   begin
    select  @sql = @sql + ' and tt.cTask like ''' +  @tx2cTask + '%'''
   end
  else
   begin
    select  @sql = @sql + ' and tt.cTask like ''%' +  @tx2cTask + '%'''
   end
 end 

  if @txtTask is not null  and @txtTask != ''
 begin
  if @BGWBeginWith =1
   begin
    select  @sql = @sql + ' and tt.cTask like ''' +  @txtTask + '%'''
   end
  else
   begin
    select  @sql = @sql + ' and tt.cTask like ''%' +  @txtTask + '%'''
   end
 end 

 if @cbonClientID is not  null and  @cbonClientID != ''
 begin
  select  @sql = @sql + ' and cli.nid in (' +  @cbonClientID + ')'
 end

 if @LSMnClientID is not  null and  @LSMnClientID != ''
 begin
  select  @sql = @sql + ' and cli.nid in (' +  @LSMnClientID + ')'
 end


 if @cbonClientID is not  null and  @cbonTaskTypeID != ''
 begin
  select  @sql = @sql + ' and tt.nTaskTypeID in (' +  @cbonTaskTypeID + ')'
 end

 if @LSMnTaskTypeID is not  null  and  @LSMnTaskTypeID != ''
 begin
  select  @sql = @sql + ' and tt.nTaskTypeID in (' +  @LSMnTaskTypeID + ')'
 end

 if @cbonTaskSubTypeID is not null and @cbonTaskSubTypeID != ''
 begin
  select  @sql = @sql + ' and tt.nTaskSubTypeID in (' +  @cbonTaskSubTypeID + ')'
 end

 if @LSMnTaskSubTypeID is not null and @LSMnTaskSubTypeID != ''
 begin
  select  @sql = @sql + ' and tt.nTaskSubTypeID in (' +  @LSMnTaskSubTypeID + ')'
 end

 if @cbonTaskStatusID is not null and  @cbonTaskStatusID != ''  
 begin
  select  @sql = @sql + ' and tt.nTaskStatusID in (' +  @cbonTaskStatusID + ')'
 end
 
 
 if @LSMnTaskStatusID is not null and  @LSMnTaskStatusID != ''  
 begin
  select  @sql = @sql + ' and tt.nTaskStatusID in (' +  @LSMnTaskStatusID + ')'
 end
 
if @source = 'dashboard_Both' 
 begin 
 if( @LSMnPrimaryContactID is not  null  and  @LSMnPrimaryContactID != '') and (@LSMnOtherContactID is not  null  and  @LSMnOtherContactID!= '')
 begin
  select  @sql = @sql + ' and ((txConPrimary.nContactID in (' +  @LSMnPrimaryContactID + ')) or (txConOther.nContactID in (' +  @LSMnOtherContactID + ')))'
 end
 end 
 else 
 begin
 if @LSMnPrimaryContactID is not  null  and  @LSMnPrimaryContactID != ''
 begin
  select  @sql = @sql + ' and txConPrimary.nContactID in (' +  @LSMnPrimaryContactID + ')'
 end

 if @LSMnOtherContactID is not  null  and  @LSMnOtherContactID!= ''
 begin
  select  @sql = @sql + ' and txConOther.nContactID in (' +  @LSMnOtherContactID + ')'
 end
end

 if @cbonPrimaryInternalContactID is not  null  and  @cbonPrimaryInternalContactID != ''
 begin
  select  @sql = @sql + ' and txConPIlist.nContactID in (' +  @cbonPrimaryInternalContactID + ')'
 end

 if @LSMnPrimaryInternalContactID is not  null  and  @LSMnPrimaryInternalContactID != ''
 begin
  select  @sql = @sql + ' and txConPIlist.nContactID in (' +  @LSMnPrimaryInternalContactID + ')'
 end

 if @LSMnTaskPriorityID is not  null  and  @LSMnTaskPriorityID != ''
 begin
  select  @sql = @sql + ' and tt.nTaskPriorityID in (' +  @LSMnTaskPriorityID + ')'
 end
 
 

 if @LSMnCreatedContactID is not  null  and  @LSMnCreatedContactID != ''
 begin
  select  @sql = @sql + ' and tt.nCreatedContactID in (' +  @LSMnCreatedContactID + ')'
 end

 

 if @LSMnClientType is not  null  and  @LSMnClientType != ''
 begin
  select  @sql = @sql + ' and cli.cType in (' +  @LSMnClientType + ')'
 end

 if @LSMcCampaign is not  null  and  @LSMcCampaign != ''
 begin
  select  @sql = @sql + ' and tt.cCampaign in (' +  @LSMcCampaign + ')'
 end

 if @tx2cNote is not  null  and  @tx2cNote != ''
 begin
  select  @sql = @sql + ' and convert(varchar(max),tt.cNote) like ''%' +  @tx2cNote + '%'''
 end

 if @cbolCompleted =1 OR @cbolCompleted =0
 begin
  select  @sql = @sql + ' and tt.lcompleted = ' +  convert(varchar(max),@cbolCompleted)
 end

 if @chkUnAssignedEntity != 0 
 begin
  select  @sql = @sql + ' and tt.cFileAreaSiteCode = '''' and txCli.nClientID is null ' 
 end
 
 if @chkUnAssignedPrimary != 0 
 begin
  select  @sql = @sql + ' and txConPrimary.nContactID is null ' 
 end

 if @MT1dDesiredDueDate > '01/01/1900' 
 begin
  select @sql = @sql  + ' and tt.dDesiredDueDate > ''' + convert(varchar(max),@MT1dDesiredDueDate) + ''''
 end

 if @MT3dDesiredDueDate < '12/31/9000'
 begin
  select @sql = @sql  + ' and tt.dDesiredDueDate < ''' + convert(varchar(max),dateadd(d,1,@MT3dDesiredDueDate)) + ''''
 end


 
 if @MT1dCreated > '01/01/1900' 
 begin
  select @sql = @sql  + ' and tt.dCreated > ''' + convert(varchar(max),@MT1dCreated) + ''''
 end

 if @MT3dCreated < '12/31/9000'
 begin
  select @sql = @sql  + ' and tt.dCreated < ''' + convert(varchar(max),dateadd(d,1,@MT3dCreated)) + ''''
 end

 if @MT1dModified > '01/01/1900' 
 begin
  select @sql = @sql  + ' and tt.dModified > ''' + convert(varchar(max),@MT1dModified) + ''''
 end

 if @MT3dModified < '12/31/9000'
 begin
  select @sql = @sql  + ' and tt.dModified < ''' + convert(varchar(max),dateadd(d,1,@MT3dModified)) + ''''
 end

 if @MT1dStartDate > '01/01/1900' 
 begin
  select @sql = @sql  + ' and tt.dStartDate > ''' + convert(varchar(max),@MT1dStartDate ) + ''''
 end

 if @MT3dStartDate  < '12/31/9000'
 begin
  select @sql = @sql  + ' and tt.dStartDate < ''' + convert(varchar(max),dateadd(d,1,@MT3dStartDate) ) + ''''
 end


 
 --select @sql = @sql  + ' and tt.dCreated between ' + convert(varchar(max),@MT1dCreated) + ' and  ' + convert(varchar(max),dateadd(day,1,@MT3dCreated))
 --select @sql = @sql  + ' and tt.dModified between ' + convert(varchar(max),@MT1dModified) + ' and  ' +  convert(varchar(max),dateadd(day,1,@MT3dModified))
 --select @sql = @sql  + ' and tt.dStartDate between ' + convert(varchar(max),@MT1dStartDate) + ' and  '  + convert(varchar(max),dateadd(day,1,@MT3dStartDate))

 --if @cbocSortOrder is not null and @cbolCompleted != ''
 --begin
 -- select  @sql = @sql + ' order by ' +  @cbocSortOrder
 --end

 
 insert into @Result  exec( @sql)

 select * from @Result
end

Open in new window

Nakul Vachhrajani

I don't have SSMS in front of me right now, but the condition you need to implement would be similar to ISNULL(@LSMnTaskStatusID, tt.nTaskStatusID) = tt.nTaskStatusID

Robb Hill

ASKER

So where would you place this and any advice on refactoring the design of proc

Jim Horn

In other words handle it as if the param was never passed to begin with.
>The parameter that gets passed in as a nulll is @LSMnTaskStatusID.

Typically I would handle this in the WHERE clause of queries like this...

SELECT yabba, dabba, doo
FROM your_table
WHERE some_id_column = @LSMnTaskStatusID OR @LSMnTaskStatusID IS NULL

Open in new window

Nakul Vachhrajani

Hello!

I checked the SP now in SSMS and see that you already have checks for the parameter being NULL (i.e. if it is NULL, skip the filter).

What is the error you get if the parameter is passed as NULL?

 if @LSMnTaskStatusID is not null and  @LSMnTaskStatusID != ''  
 begin
  select  @sql = @sql + ' and tt.nTaskStatusID in (' +  @LSMnTaskStatusID + ')'
 end

Open in new window

Robb Hill

ASKER

I am running this stored proc as the primary datasource of a ssrs report. When it calls the report in sSri or passes this in the query string as equal to null. So when the report runs it returns no value. If I remove that parameter from the query string it returns the values just fine.

ASKER CERTIFIED SOLUTION

Nakul Vachhrajani

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robb Hill

ASKER

thank you so much. That was driving me nuts;)

Nakul Vachhrajani

Glad I could be of help :) - have a great week-end!