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

• Kate: Instead of ISNULL you can write: IF <expression> IS NOT NULL...
• Sean Lange: Be careful here. This pattern leaves you extremely vulnerable to sql injection.

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)