Hello Jeevan,

Hope you're doing well.

What exactly is the error that you're getting?

The condition WHERE @param IS NULL OR @param = '' OR {entity}.id IN(@param) becomes WHERE IS NULL OR = '' OR {entity}.id IN() when @param is empty. This is syntactically incorrect, leading to the error.  Hence it is better to conditionally execute the SQL query only if your list is not empty.

Hi @Jeevan Sai Sabbavarapu, 

Can you please share the sample OML or screenshot of the error message that you are encountering. 

Thanks,
VP.

Hi @Jeevan Sai Sabbavarapu 

Use this query:

SELECT *

FROM {entity}

WHERE CASE

    WHEN @param IS NULL THEN TRUE//it return all records.

    ELSE @param IN ({entity}.id)

END;

Hope it helps,Thanks.

Thanks

Karnika

Hi Jeevan,
Hope you are doing well.
Can you please share data type of @Param also screenshot of error you are facing.

Hello @Jeevan Sai Sabbavarapu
try below

where case when @param is null then true else  {entity}.id IN(@param) 
but before using input parameter in query, you need to mark it as expand inline to yes.

Hi @Jeevan Sai Sabbavarapu 

You can add another parameter (of type boolean) to your query, and write the following condition. This way, the query will return all users if @UserId is null, otherwise, it will filter the users based on the provided @UserId list.

In the Sql properties you must pass:

Note: The inline parameter must be sanitized before being passed to the SQL. You can use the BuildSafeText action which will return a safe in clause string. 

Regards,

Tami

Hi Jeevan,

Input parameters can never by NULL unless they are Identifiers. In your case, for "IN (@param)" to work, @param needs to be an Expand Inline parameter, which means it's the literal text pasted into your query. Which means that when it is empty, you'll get a syntax error in your SQL, as your "IN (@param)" evaluates to "IN ()", which is illegal. Therefore you'll need to ensure that @param at least contains a value, that won't match. So for an integer/identifier list, it's probably "0", and for a text identifier list or text list, its "''" (two single quotes). In that case, you can then do something like:

WHERE @param = '' OR {Entity}.[TextAttribute] IN (@param)

or

WHERE @param = 0 OR {Entity}.[Id] IN (@param)

Guys,

If my answer is not working as expected, then suggest your idea by replying to my comment but don't do thumbs down.

@Jeevan Sai Sabbavarapu