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.

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

Query looks like this:

"SELECT * FROM table
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 :

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

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

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.