Advanced Parameters

If you are simply looking for input parameters to use in your where tab in the tile-based query builder, you are best to view the general parameter page.

This page covers the more sophisticated parameters that can be used to markup complex SQL not supported by the standard tile-based query builder, or if you are using manual text mode.

Adding Parameters via the Tile-Based Query Builder

Parameter tiles are added to your query with the tile-based query builder by pressing the @ icon in the designer canvas quick bar on the Select, Join Criteria, Where, Group and Order tabs.

If you have defined any previous parameters, the pop up will allow you to re-add the same parameter, or select new to create a new one.

You can change the parameter type (described below), by using the Type drop down on the Output Columns or Input Parameters found in the Data Preview tab in the overview panel.

You can set a test value for input parameters by using the Value entry box.

Types of Parameter

In

An input parameter. This parameter is required to be passed in through the web service call, and can be used throuighout the query.

Syntax
@paramName or @in:paramName

Example
SELECT TotalSales * @in:comRate as [SalesCommision]
FROM Sales
WHERE Account = @accountNumber

Out

An outbound parameter which is generated following the preview of the query.

Syntax
@out:paramName

InOut

For use within stored procedures and functions. This is sometimes the result of an operation is returned in a parameter which is designated the RETURN_VALUE.

Syntax
EXEC ReverseString(@inout:stringToReverse)

Constant

This marked parameter can be used throughout the query, but will not expect a web service parameter as it is defined at design time. It will replace that value over all occurances of the parameter pre-query. Constant strings are quoted when injected back into SQL. Use the type object if you wish to avoid using quotes to inject SQL.

Syntax
@constant:paramName

Example
SELECT @constant:surname as Surname, sum(TotalDue)
FROM OrderHeader
WHERE SalesPersonID in (select SalesPersonID from SalesPerson where LastName = @constant:surname)

When the surname value is set to Smith, the output always contains Smith, good for script versioning etc.

Inline

This marked parameter can be used throughout the query, and will expect a web service input parameter. It will replace that value over all occurances of the paramater pre-query. Constant strings are not quoted, and should be quoted if required. Alternatively column names or any SQL can inbjected into the SQL with this parameter.

Syntax
@inline:paramName

Example
SELECT @inline:surname as Surname, Sum(TotalDue)
FROM OrderHeader
WHERE SalesPersonID in (select SalesPersonID from SalesPerson where LastName = @inline:surname)

The value of surname is applied at runtime, and could be 'Smith', 'Brown', etc.

ReturnValue

For use within Store Procedures/functions, sometimes the result of an operation is returned in a parameter which is designated the RETURN_VALUE. Use this syntax in custom SQL to get it to output as a column.

Syntax
@returnvalue:paramName

Special Parameters

Parameters that require values can use special values. Typically you would use these with Constant type parameters.

The special values are available in the Value input parameter entry box, which also functions as a drop down.

<%WindowsUser%>
Replaces this marker with the current windows user name. To see the user of the web service you must switch to Windows Authentication in your Web Service publication properties.

<%ServerName%>
Replaces this marker with the current server name.

<%Request:PATH_INFO%>
Looks at the Request object in the web service call and extracts the appropriate Request property. This could be used to get any passed parameters in the URL, cookie or form data.

<%TimeStamp:yyyyMMddHHmmss%>
This is replaced with the current date time of the web service call.

Working with Unsupported SQL Dialects

If DataConnect has difficulty parsing your SQL correctly, you can force the designer to stop attempting to parse at all. For this to work you must mark everything (in and out) with parameters.

To indicate you wish to mark up everything manually, you use the tilde ~ mark instead of the @ symbol. As soon as DataConnect sees the ~, it stops trying to carry out any automatic parsing.

This is where a selected column is called MyColumn, and where you have an input parameter called inputValue. You set the test value for inputValue in the Value column of the Input Parameters section. 

This means no matter how unusual your SQL syntax is, you can always force DataConnect to behave.