SQL Parameters snippet

Description

The SQL Parameters snippet is a useful tool to organize and maintain the required SQL parameters field.

The SQL parameters snippet would be used in some AlchemyJ Extended Functions such as ajDBRunSQL and ajDBRunStoredProc.

To insert a SQL Parameters snippet, select a cell in an empty row and ensure that there are 4 extra empty rows below it to prevent the snippet from overwriting the values below.

Go to AlchemyJ ribbon and click Insert Snippet. Under the dropdown, select SQL Parameters.

Column definition

Column Name	Description
Name (mandatory)	This column is for the parameter name.
Value (mandatory)	This column is for the parameter value.
Type (mandatory)	This column is for the parameter type. The data type is different for different database, please refer to Supporting Type for Different Database.
Direction (optional)	This column is for the parameter direction. IN - Values that would be passing in OUT - Values that would be returned The default value would be 'IN' if no value is provided in this column.

Supporting Type for Different Database

Oracle	MySQL	MSSQL	PostgreSQL
CHAR	BIGINT	BIGINT	BIGINT
DATE	CHAR	BIT	BOOLEAN
DEC	DATE	CHAR	BPCHAR
DECIMAL	DATETIME	DATE	CHAR
FLOAT	DECIMAL	DATETIME	DATE
INT	DOUBLE	DATETIME2	DOUBLE
INTEGER	FLOAT	DATETIMEOFFSET	INT
LONG	INT	DECIMAL	MONEY
NCHAR	LONGTEXT	FLOAT	NAME
NUMBER	MEDIUMINT	INT	NUMERIC
NUMERIC	MEDIUMTEXT	MONEY	OID
NVARCHAR2	SMALLINT	NCHAR	SMALLINT
REAL	TEXT	NTEXT	TEXT
SMALLINT	TIME	NUMERIC	TIME
TIMESTMP	TIMESTAMP	NVARCHAR	TIMESTAMP
VARCHAR2	TINYINT	SMALLDATETIME	TIMESTAMPTZ
	TINYTEXT	SMALLINT	TIMETZ
	VARCHAR	SMALLMONEY	VARCHAR
	YEAR	TEXT
		TIME
		TIMESTAMP
		TINYINT
		UNIQUEIDENTIFIER
		VARCHAR
		XML

Notes: When Type is Date, only 'yyyy/MM/dd' and 'MM/dd/yyyy' date format are supported.

Example

Below is the example of the ajDBRunSQL function. Insert the SQL Parameters snippet.

The snippet will be populated as shown below with no values.

Fill in the SQL parameters details accordingly.

When referencing the SQL Parameters table, do remember to include the column headers as part of the cell range as shown in Cell B15 for the formula to work.