Executing SQL Functions in Coldfusion

Question

I have a user defined function in my MS SQL(2005) that I would like to execute through Coldfusion (8). Any idea how I can get this to work?

[It's not a stored procedure, so cfstoredproc is out] .

Thank you in advance.

Answer 1

You can call the SQL UDF right inside a regular CFQUERY block, assuming your Coldfusion datasource has appropriate EXECUTE permissions on the desired UDF. You don't have to return a select statement inside of CFQuery. Remember to use CFQUERYPARAM for safety if you are passing any Coldfusion variables to the database.

To call the UDF, you need to include its schema*. In our case, we used the default schema, so our UDFs are prefixed with "dbo." as in dbo.FunctionName().

Here is an example:

<!--- We want to convert this numeric category into its English name.
      Thankfully our database administrator has a simple function to 
      resolve it without extra work on our part. --->

<cfset myCategory = 100428>

<!--- We call this Coldfusion variable using CFQUERYPARAM to prevent SQL Injection --->

<cfquery datasource="mydatasource" name="test">
  SELECT dbo.CategoryAsString(<cfqueryparam cfsqltype="cf_sql_integer" value="#myCategory#">) AS CategoryString
</cfquery>

<!--- And output the result here --->
<cfdump var="#test.CategoryString#">

*If you don't include the UDF's schema, you will get an "Error Executing Database Query. [Macromedia][SQLServer JDBC Driver][SQLServer]'[FUNCTION NAME]' is not a recognized built-in function name." (CF 8)