CFLib.org – Common Function Library Project

Description:
Convert Microsoft SQL Server Data Type to equivilent CFSQLType. Most usefull when querying MS SQL Server sys tables to return data type from the table structure and converting to a CF data type for use within the <cfqueryparam> tag.

Return Values:
returns a string

Example:

#MSSQL2CFSQLDT('nvarchar')#  returns  CF_SQL_VARCHAR

Parameters:

Full UDF Source:

<cfscript>
/**
 * Convert Microsoft SQL Server Data Type to equivilent CFSQLType
 * Most useful when querying MS SQL Server sys tables to return data type from the table structure and converting to a CF data type for use within the &lt;cfqueryparam&gt; tag.
 * 
 * Example Usage
 * #MSSQL2CFSQLDT('nvarchar')#  returns  CF_SQL_VARCHAR
 * 
 * Author C. Jason Wilson (&#99;&#106;&#119;&#105;&#108;&#115;&#111;&#110;&#64;&#99;&#102;&#45;&#100;&#101;&#118;&#101;&#108;&#111;&#112;&#101;&#114;&#46;&#110;&#101;&#116;) 
 * version 1, January 13, 2009
 * 
 * @param dataType 	 String of datatype to use (Required)
 * @return returns a string 
 * @author C. Jason Wilson (&#99;&#106;&#119;&#105;&#108;&#115;&#111;&#110;&#64;&#99;&#102;&#45;&#100;&#101;&#118;&#101;&#108;&#111;&#112;&#101;&#114;&#46;&#110;&#101;&#116;) 
 * @version 0, May 9, 2009 
 */
function MSSQL2CFSQLDT (DataType) {
	var MSSQLType = 'int,bigint,smallint,tinyint,numeric,money,smallmoney,bit,decimal,float,real,datetime,smalldatetime,char,nchar,varchar,nvarchar,text,ntext';
	var CFSQLType = 'CF_SQL_INTEGER,CF_SQL_BIGINT,CF_SQL_SMALLINT,CF_SQL_TINYINT,CF_SQL_NUMERIC,CF_SQL_MONEY4,CF_SQL_MONEY,CF_SQL_BIT,CF_SQL_DECIMAL,CF_SQL_FLOAT,CF_SQL_REAL,CF_SQL_TIMESTAMP,CF_SQL_DATE,CF_SQL_CHAR,CF_SQL_CHAR,CF_SQL_VARCHAR,CF_SQL_VARCHAR,CF_SQL_LONGVARCHAR,CF_SQL_LONGVARCHAR';
	
	if(ListFind(MSSQLType,DataType)) {
		return ListGetAt(CFSQLType,ListFind(MSSQLType,DataType));
	} else { return 'NULL'; }
}
</cfscript>