Is there a more concise way to write this Procedure

Question

I have this stored Procedure that is passed in the data below that will building the SQL select that will get EXEC. The data that can be passed in can me null so I have three IF ISNULL checking and then adding to the @SqlWhereCommand object. Any help would be great.

parameter

@StartDate AS DATE = NULL, 
@EndDate AS DATE = NULL, 
@Ids As Varchar(500) = NULL, 
@SubCategories As Varchar(300) = NULL, 
@LanguageIds As Varchar(300) = NULL, 
@RegionIds As Varchar(300) = NULL, 

DECLARE

DECLARE @SqlCommand nvarchar(4000)  
DECLARE @SQLColumnList varchar(2000) 
DECLARE @SqlSelectCommand nvarchar(4000)  
DECLARE @SqlFromCommand varchar(200)  
DECLARE @SqlWhereCommand nvarchar(2000)  
DECLARE @SqlGroupCommand nvarchar(4000) 

Buildings the Selects

SET @SQLColumnList =' Id, [Name] as ''gl.Name'', type = ''PDF'', CONCAT([Description],''Data Stats'') as Description, 
SUM (WebsiteDownloads) As Downloads,[Language],
Region = ''Asia'''
        
Set @SqlFromCommand =' from [report].[Downloads]'
Set @SqlGroupCommand =' GROUP BY Id, [Name],[Description],[Language]'
Set @SqlWhereCommand = ' where cast(LogDate as date) >= + ''' + CONVERT(VARCHAR(25), @StartDate, 120) + ''' and cast(LogDate as date) <= ''' + CONVERT(VARCHAR(25), @EndDate, 120) + ''''       



IF ((ISNULL(@Ids,'')) <> '')  
    BEGIN 
        SET @SqlWhereCommand = @SqlWhereCommand + ' AND Id IN (SELECT val AS linkType FROM dbo.Split(''' + @Ids + ''','',''))' 
    END

IF ((ISNULL(@SubCategories,'')) <> '')  
    BEGIN   
        SET @SqlWhereCommand = @SqlWhereCommand + ' AND LinkTypeId IN (SELECT val As subCategorys FROM dbo.Split(''' + @SubCategories + ''', '',''))' 
    END 

IF ((ISNULL(@LanguageIds,'')) <> '')  
    BEGIN 
    SET @SqlWhereCommand = @SqlWhereCommand + ' AND LanguageId IN (SELECT val As subCategorys FROM dbo.Split(''' + @LanguageIds + ''', '',''))' 
END 

SET @SqlCommand = 'SELECT ' + @SQLColumnList + @SqlFromCommand + @SqlWhereCommand + @SqlGroupCommand

Answer 1

Not anything groundbreaking, but I use the template replicate(myString,aFlag) to decide to include a string or not. Also, you don't really need the isnull() here, because if your variable is indeed null, all checks fail, including <>''.

Here:

SET @SqlWhereCommand = @SqlWhereCommand
+replicate(' AND Id IN (SELECT val AS linkType FROM dbo.Split(''' + @Ids + ''','',''))' , case when @Ids <> '' then 1 else 0 end)
+replicate(' AND LinkTypeId IN (SELECT val As subCategorys FROM dbo.Split(''' + @SubCategories + ''', '',''))' , case when @SubCategories <> '' then 1 else 0 end)
+replicate(' AND LanguageId IN (SELECT val As subCategorys FROM dbo.Split(''' + @LanguageIds + ''', '',''))' , case when @LanguageIds <> '' then 1 else 0 end)