Is there a more concise way to write this Procedure
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
lang-sql