SQL Server UDF Function

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JTMK - 04 Jul 2008 12:05 GMT

Hi, first time poster and new to Coldfusion!!!

I have created a udf in SQL Server that accepts a staff
number and returns an integer. This is then called from within a component as
follows:

<cfquery name="qUDF" datasource="#***********#">
SELECT dbo.GetNumber('#intStaffNumber#')
</cfquery>

How do I get hold of the returned value in Coldfusion? I need to save it to a
variable and display it on screen.

Thanks in advance.

Reply to this Message

Daverms - 04 Jul 2008 12:18 GMT

Hi JTMK,

Welcome to Coldfusion!...

Write your query as,

<cfquery name="qUDF" datasource="#***********#">
SELECT dbo.GetNumber('#intStaffNumber#') as staffno
</cfquery>

and to print the retrieved value like this,

<cfif isdefined('qUDF') and qUDF.recordcount gt 0>
<cfoutput>#qUDF.staffno#</cfoutput>
</cfif>

Reply to this Message

-==cfSearching==- - 04 Jul 2008 12:55 GMT

> How do I get hold of the returned value in Coldfusion
> I need to save it to a variable and display it on screen.

Welcome JTMK.

To expand a little further, you can access the returned UDF value directly
through your query object "qUDF". However, you need to give it an "alias".

<cfquery name="qUDF" datasource="#***********#">
SELECT dbo.GetNumber('#intStaffNumber#') [b]AS SomeAliasHere[/b]
</cfquery>

Now you can access the value directly, using the query name and selected
alias. Since your udf is scalar (returns a single value only), you can output
the value with a simple <cfoutput>. No need to use additional variables.

<cfoutput>
#qUDF.SomeAliasHere#
</cfoutput>

You can read more about cfquery and cfoutput here:
http://livedocs.adobe.com/coldfusion/8/Tags_p-q_17.html
http://livedocs.adobe.com/coldfusion/8/Tags_m-o_16.html

Reply to this Message

JTMK - 04 Jul 2008 13:52 GMT

Thanks for your help!

Reply to this Message

Webmaster Forum / ColdFusion / Database Access / July 2008

SQL Server UDF Function