Pass Wildcard Parameters on Named Queries

Hello Everybody!,

I’d like to setup a Named Query that selects rows based on WHERE conditions. In some use cases, I’d like to pass a wildcard (*) for a parameter. So far, it seems like the only solution is to create a new Named Query with an omitted WHERE condition.

What do you guys think?

Sounds like you could utilize the SQL ‘LIKE’ operator.

https://www.w3schools.com/sql/sql_like.asp

What is your query? and where do you want this wildcard?

Query looks like this:

"SELECT * FROM table
WHERE
Column1 = :String1
AND Column2 = :Int1
AND Column3 = :Int2
"

Query works great if I run system.db.runNamedQuery, but in some cases I’d like to grab rows that only match the first two arguments and pass a wildcard integer for :Int2 .

How would I put a wildcard in the python dictionary or should I add ‘LIKE’ operator like @ben.d.musson said?

Thanks guys

You can’t use LIKE in your instance.

You can use this :

SELECT * FROM table
WHERE
Column1 = :String1
AND Column2 = :Int1
AND (CAST(Column3 as nvarchar) = :Int2 OR :Int2 = '*' ) 

Note: your “int2” would actually need to be a string type parameter, that, or add a new parameter called Int2Enable

SELECT * FROM table
WHERE
Column1 = :String1
AND Column2 = :Int1
AND (Column3 = :Int2 OR :Int2Enable <> 1 ) 

You could use a query parameter instead, but then you would rely on people supplying the right string (mentioned this just for other ideas, definitely not the best option though) .

LIKE doesn’t work for integer columns, only for string columns. I would make another named query. IA isn’t charging you for extra ones, after all. :smile:

1 Like

Something that I do quite frequently is to add a 1 = Case statement like the following, allows you to just pass a null if you want to “ignore” the check for a certain column

SELECT * FROM table WHERE 
1 = (CASE WHEN :Int1 IS NULL THEN 1 WHEN :Int1 = Column1 THEN 1 ELSE 0 END) AND
1 = (CASE WHEN :Int2 IS NULL THEN 1 WHEN :Int2 = Column2 THEN 1 ELSE 0 END) AND
1 = (CASE WHEN :String1 IS NULL THEN 1 WHEN :String1 = Column3 THEN 1 ELSE 0 END)

In this case you might pass it the following to ignore the Column2 check
Int1 = 1, Int2 = NULL, String1 = “Test”

You can change the WHEN :Int1 IS NULL THEN 1 To whatever you like to ignore that parameter, If null is a valid parameter in this case then maybe you use WHEN :Int1 = -1 THEN 1

Note, I also typically put these in stored procedures, as I find keeping as much SQL code on the SQL server as possible is easier to manage, so I am really just executing the named query as

EXEC dbo.StoredProcedure :Int1, :Int2, :String1

Note: you won’t be able to use NULL in a named query, unless maybe you’re using the script function and can actually pass in nulls in (not sure if possible). You can’t pass nulls into a binding though

1 Like

Good point @nminchin

I forgot to mention this, I typically use scripting for named query calls, however to use the property bindings you would need to use something like a -1 or empty string “” to accomplish this.