[00:00] In a previous video, we showed how to create a simple named query that did not use any parameters. When executing a named query, parameters may be passed to it to return a dynamic results set. Here we have a simple query that returns all the data from an inventory table, which is inside my database named DB. Go into the testing tab and executing my query, I see that it returns five columns. What if I only want this query to return inventory data for a specific flavor? To do this, I will need to modify the query to take in a flavor parameter and use it in a sequel where clause to filter data specific to the flavor I pass onto my named query. Let's see what this will look like. In the authoring tab, I will first create a parameter named My Flavor. I will set its parameter type to value. The value of parameter type should be used whenever a named query needs a dynamic WHERE clause, the idea being that we pass a value to the WHERE clause condition to make it dynamic.

[01:05] The flavor column in my database is of type string, so I will select my parameter's data type to also be a string. Once my parameter is created, I will modify my query to have a where clause to make the query only return inventory data that has a flavor that matches my parameter's value. Notice how I referenced my parameter using a colon followed by my parameter name. You can also right click on the query and select insert parameter and the parameter will be automatically added to the query with the correct syntax. Going back to the testing tab, we see that the parameter, My Flavor, appears in the test parameters table. If I type chocolate in the value field and execute my query, I see that only the data for chocolate gets returned. Similarly, typing vanilla and executing the query only the data for vanilla will be returned. Currently, our named query is returning all the columns in the inventory table. Let's say that we want to dynamically choose what column our query returns.

[02:02] To do this, we go back to the authoring tab and we create an additional parameter named My Column. I will set this parameter type to query string and set its data type to string. Query strings are more flexible than the value type in that they can be used to parametrize column and table names. However, their values are never sanitized, which causes them to be more susceptible to SQL injection attacks. When using query strings, it is best to avoid situations where the user can manually type in the value that will be passed to the named query. Now that my parameter has been created, I will add it to my query select statement so I can tell the query to select a dynamic column from the inventory table. Now that my query has been updated, we go back to the testing tab and we see that the parameters table now list the parameter, My Column. If I pass a column named flavor to the My Column parameter and execute the query, we see that the query only returns the flavor column. Similarly, if I pass a column named Count to the My Column parameter and execute the query, we see that the query only returns the Count column.