Skip to main content
Windward

SQL Selects

Overview

This article goes over miscellaneous features related to SQL selects.

Select Modes

Version 10

A select can be evaluated in one of three ways:
  • !select - Where the select is passed to the underlying data source.
  • =evaluate - Where the select is evaluated by Windward's macro evaluator.
  • 'value - Where the select is placed as a string of text in the output.

Windward will uses a set of rules to guess as to which of the above modes to use. However, you can explicitly force a specific mode by setting the first character of the select to !, =, or ' (as shown above on each item).

AutoTag will always display the mode it is using on a select in the UI. In the tag builder if you set it to force a mode, it will place the first character only if that is not the guessed mode for that select.

Stored Procedures

Version 10  

Stored procedures have a very specific syntax that must be used. This is not the exact syntax used in a standard select because we need to know the data type of each parameter and the windward engine cannot determine the type (AutoTag can). In addition, we only support stored procedures that return a result set.

 

The format of a stored procedure call is:

exec procedure parameter1=N'value', parameter2=D'value', parameter3=S${var}

There is a sample template using the standard sample databases at "Sql Server - Northwind" and "MySql - Sakila".

The parameter= part must include every input parameter and you use the name of the parameter.

The value is the Type followed by the value itself. The value can be a literal ot a ${var}. The type is optional and if not used, will be the native type for a ${var} and string for everything else.

There is also the special case of parameter='OUT' and parameter='RETURN' which are used for vendors that require variables of that type to be declared on a call (MySql and DB2 requires output parameters to be declared). The return values are ignored.

The allowed type values are:

D - DateTime

N - Number (integer or floating)

S - String

b - Byte

s - Single (16-bit integer)

i - Integer (32-bit integer)

l - Long (64-bit integer)

f - Float

d - Double

c - deCimal (and Currency)

o - bOolean

Testing NULL values

Version 10  

To test the contents of a column for null (as opposed to empty), test it for the value 'DbNull' - that will be converted internally to test for a null result. This does mean that you cannot test a string column for the actual text DbNull. 

  • Was this article helpful?