SQL, checking multiple dynamic parameters

Ignition
1

I am trying to figure out how how I can configure the parameters in my where clause for my query to receive a list of the Skus to evaluate in the sorting.

I know I need to work on my ranking too, but I think I can get that easy enough.

select  
rank() over (partition by Line order by Net desc, Sku) as LineRank, Sku, Net, Scrap
from myTable
where Sku in (     )
group by Line

If I have like 20 skus, I don't want to do a big case statement.
I am seeing some different implementations online, but none are really the thing.

I thought maybe I could pass a byte array that gets set from an expression tag binding.

These were close:

sql server 2008 - SQL Where clause with dynamic IN() operator - Stack Overflow

I think this one might work, where he passes a string that he parses. I might do this implementation.

c# - Execute stored procedure with array-like string separated by comma - Stack Overflow

2

You can use a querystring instead of a value,

You'll have to parse the value correct ofc with quotes and commas

image
image904×261 7.55 KB

image
image1238×348 13.3 KB

1 Like
3

Are you using a named query or a scripting function?

1 Like
4

I planned to use a named query.
edit: If I ran a script, I use named queries runPrepUpdate in those cases.

Thanks Victor
I think that is what I will use. Looks efficient.

5

You should never use queryString parameters with user input. You can sanitize your dropdowns with indirection, but any text search fields are a security breach waiting to happen.

If scripting, construct "Prep" queries with the correct number of question marks and make sure your argument list matches. If not scripting, reconsider.

3 Likes
6

As a specific example of @pturmel's suggestion:

1 Like
7

Thanks for helping me.

I wasn't using queryString.

I was using a table component with a named query that had parameter to bind to a component on the perspective page.

I use runPrepUpdate() in scripts.
I had a typing error in my previous post. Sorry for the confusion.

Oh, Victor's example is a queryString. I did not realize that till just now.

8

I will probably use @zacht's example code in a script.

Before I get my list of Skus to get ranks though:

What would be a good component to let users efficiently pick which of 20 Skus they wanted to utilize in the query?

I think clicking on 20 radial dials or check boxes would be terrible.
I would like to have maybe a drag selection maybe if possible, so maybe a user could draw a box around some to add to the current list.

Or maybe I can have them in a table that they can click and drag on that I could color, is that doable?