Parameters

Parameters

HelpID: 460022039

The first step to creating a query is to write the query in Query Analyzer (SQL Management Studio) to ensure the syntax is correct. The Query window in Consulting Toolkit can also be used.

The next step is to create the parameters. When a stored query is executed, the user will be prompted to enter Parameters (if there are any). For example, perhaps the user wants to find all item numbers that begin with a specific user entered string (i.e. all items beginning with ABC). The SQL query would look like this:

Select * from IV00101 where ITEMNMBR like ‘ABC%’

The Stored Query using a parameter would look like this:

Select * from IV00101 where ITEMNMBR like ‘{1}%’

Click the PARAMETERS button to open the Parameters window.

The first parameter you create will have ID {1}, and the second will be {2} and so on. The ID is auto-generated. After creating parameters, add the numbered parameters into the Query in the place where you want to substitute user entered data.

Select * from IV00101 where ITEMNMBR like ‘{1}%’

Delete a parameter by selecting the row, then clicking Edit >> Delete Row.

Query Builder Window:

Enter the tSql query. To ensure correct syntax is used, the query should be created in Query Analyzer and pasted into this window. A simple query is:

Select * from IV00101

Parameters are added by inserting the Parameter ID. For example:

Select * from IV00101 where ITEMNMBR = ‘{1}’

Note that the apostrophes before and after the parameter are included in the query because it is searching for a string. The parameter {1} will be replaced by the user entered data.

When the Execute Stored Query window runs a query with parameters, it replaces only the parameter, such as {1}, with the data entered by the user. Here is another example showing a numeric parameter:

Select * from IV00101 where ITMTRKOP = {1}

This query would retrieve all item numbers where the Item Tracking Option = (the value entered by the user). In this case there are no apostrophes because the user entered data (the Parameter) is a number.

When designing a Stored Query, keep in mind that the parameter will be replaced with the user entered data as-is.