Should I Use a Dynamic "Get" Function?

[Kory Gorsky]

So let's say I have a table called "Book", and it has the columns "ID, Name, Author, ISBN"

I'm wondering what the Pros and Cons would be to creating a function to get books by any attribute using one function:

<cffunction name="GetBook">

<cfargument name="Identifier" type="string">

<cfargument name="Value" type="string">

<cfquery name="Books">

SELECT ID, Name, Author, ISBN FROM Book

WHERE #Arguments.Identifier# = <cfqueryparam cfsqltype="cf_sql_varchar" value="#Arguments.Value#">

</cfquery>

<cfreturn Books>

</cffunction>

So that I could call the following functions:

GetBook('ID', 7);

GetBook('Name', 'To Kill a Mockingbird');

GetBook('Author', 'John Gisham');

GetBook('ISBN', '0321515471');

Thoughts?

[Dan_Bracuk · Accepted solution]

The concept is fine.  The implementation needs a tiny bit of work. Specifically, you want to create a local variable to determine the correct cfsqltype for your queryparam tag.

[Kory Gorsky]

So what you're suggesting for instance would be:

<cfif IsDate(Argument.Value)>

<cfset cfsqltype = 'cf_sql_timestamp'>

<cfelseif IsNumeric(Argument.Value)>

<cfset cfsqltype = 'cf_sql_numeric'>

<cfelse>

<cfset cfsqltype = 'cf_sql_varchar'>

</cfif>

<cfquery name="Books">

SELECT ID, Name, Author, ISBN FROM Book

WHERE #Arguments.Identifier# = <cfqueryparam cfsqltype="#cfsqltype#" value="#Arguments.Value#">

</cfquery>

[Dan_Bracuk]

That's one way.  Remember to use the var keyword somewhere to keep that variable local to the function.  Another way, probably more bulletproof, is to send the datatype as an argument.

[Kory Gorsky]

Thanks Dan!