0
\$\begingroup\$

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
\$\endgroup\$
2
  • 1
    \$\begingroup\$ Instead of ISNULL you can write: IF <expression> IS NOT NULL... \$\endgroup\$
    – Kate
    Jun 30, 2023 at 19:18
  • 2
    \$\begingroup\$ Be careful here. This pattern leaves you extremely vulnerable to sql injection. \$\endgroup\$
    – Sean Lange
    Aug 31, 2023 at 15:50

1 Answer 1

3
\$\begingroup\$

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)
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.