Optional prompt and mandatory filter with the data type and default value specified

Note the following:

  • This prompt allows the user to supply a valid integer response.
  • The DefaultText argument is specified. Therefore, the user may omit entering a value, in which case the value 10 is used. This makes it an optional prompt, but not an optional filter.

Example 1:

select
	COUNTRY_MULTILINGUAL.COUNTRY_CODE as COUNTRY_CODE,
	COUNTRY_MULTILINGUAL.COUNTRY as COUNTRY,
	COUNTRY_MULTILINGUAL."LANGUAGE" as LANGUAGE1,
	COUNTRY_MULTILINGUAL.CURRENCY_NAME as CURRENCY_NAME 
from 
	gosales.gosales.dbo.COUNTRY_MULTILINGUAL COUNTRY_MULTILINGUAL
where COUNTRY_MULTILINGUAL.COUNTRY_CODE > 
#prompt('Starting CountryCode',
			'integer',
			'10'
)#

Example 2:

[gosales].[COUNTRY].[COUNTRY] = #prompt('countryPrompt','string','''Canada''')#
	Result 2: 
	[gosales].[COUNTRY].[COUNTRY] = ‘Canada'

Note the following:

  • The defaultTextparameter must be specified such that is literally valid in the context of the macro, because no formatting takes place on this value. See details below.
  • The default string Canada in Example 2 is specified as a string using single quotes, in which the embedded single quotes are doubled up, thus 3 quotes. This results in the string being properly displayed within single quotes in the expression.
  • As a general rule for the string datatype, the defaultText should always be specified as in the previous note, except in the context of a stored procedure parameter.
  • For the defaultText of types date or datetime, a special format should be used in the context of SQL. Examples of these formats are 'DATE ''2012-12-25''' and 'DATETIME ''2012-12-25 12:00:00'''. In all other contexts, you use the date/datetime without the keyword and escaped single quotes (e.g., '2012-12-25').