Mount a dynamic sql with a lots of filters
Discussion

Hello, we have a screen with advanced filters and some business complexity between them. We have chosen to use an advanced SQL to build it but we have run into a wall when we try to make the filters optional.

If we fill the where with something like this (I put an example easy to see):

where .... and ({UserExterno}.[Office] = IIF(@oficinaId IS NULL or @oficinaId ='', {UserExterno}.[Office], @oficinaId))

It will not take into account when the office is null if the parameter comes to blank, because it will take the declaration:

{UserExterno}.[Office] = {UserExterno}.[Office]

I don't know how to add an option there that indicates

or {UserExterno}.[Office] is null

In other technologies, what we would have done is to mount a local parameter type varchar that would mount the SQL and end in an execute:


SET @SQL = 'SELECT ... FROM ... WHERE 1=1' 

IF @Oficina IS NOT NULL 
    SET @SQL = @SQL + ' 
        AND {UserExterno}.[Office] = @Oficina';
 
  
EXEC sp_executesql @SQL;


Nor do I see how to do it with Advanced SQL, so the only thing I can think of is to go for a stored procedure ... I think I'm complicating my life for something so silly ... Any recommendations?

Hi CrisSanz,

You could still take the same approach here, importing the entire query as an input parameter into the sql widget after carefully building it up based on what filters are applied.

Or you could have something as simple as

{UserExterno}.[Office] = @oficina or @doNotUseOfficinaFilter

Where the second is a boolean input parameter you set to true if the officina filter is not used.

Or you could even more simple go with

{UserExterno}.[Office] = @oficina or @oficina = 0

What you can not do is compare oficina with NULL, that is not available on Outsystems variables.

Dorine 

mvp_badge
MVP

Hi CrisSanz,


You can simplify by having:

where .... and (@oficinaId IS NULL OR @oficinaId ='' OR {UserExterno}.[Office] = @oficinaId)


So basically, the filters are either null or you apply the filter.



Kind Regards,
João

Similar to the solutions mentioned before, you could add another parameter just to have the NullIdentifier() (or another 'default' value) available inside the SQL Query:

{UserExterno}.[Office] = @OficinaId or @OficinaId = @NullIdentifier

This might be helpful to keep the syntax familiar, and since you are mentioning a large amount of filters, you could potentially reuse these if they have the same Data Types. For different data types (or default values) you would have to add additional parameters as needed.


Thanks a lot for the answers, I finally used the next option and it worked perfect!

(@oficinaId IS NULL OR @oficinaId =0 OR {Oficina}.[OficinaId] = @oficinaId)

Thanks!

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.