Converting ColdFusion Code to TSQL

[cutie369]

I need to write a SP for a search query from this ColdFusion code. I am not sure how to transform the coldfusion if statements in TSQL to where it can work. Any help would be appeciated.

[JR__Bob__Dobbs-qSBHQ2]

See sample below.

I reccommend "The Guru's Guide to Transact-SQL" and "The Guru's Guide to SQL Server Stored Procedures, XML, and HTML" by Ken Henderson if you're looking to learn TSQL beyond the SELECT, UPDATE, INSERT, DELETE statements.

edit: added COALESCE to 2nd part of WHERE clause

[cutie369]

I tried your suggestion and it works great when I am using SQL Query analyzer. I get an empty query result set back set back on all Stored Procedure calls. I tried doing just a straight query and it works fine. Can you look over my code please and tell me what might be causing this.

[JR__Bob__Dobbs-qSBHQ2 · Accepted solution]

The dbvarname is not supported in CF 6 or 7. I do not see it in the documentation for CF 8 either although it I read somewhere that it "may" be supported. There is also a hotfix for CF7 to enable support for dbvarname. http://kb.adobe.com/selfservice/viewContent.do?externalId=kb400074

Without dbvarname you should specify all your parameters in the order they appear in the stored procedure's definition. To specify NULL as the value of a parameter set null="yes" in the cfprocparam.

To treat an empty string as NULL
<cfprocparam value="#form.myField#" cfsqltype="cf_sql_varchar" maxlength="50" null="#YesNoFormat(Trim(form.myField) eq '')#" />



To ease creating the cfstoredproc code block run this on your SQL server in SQL Management Studio with query results sent to text. You can then copy the output to your CF file. Note this was written for MS SQL 2005, but *should* work on 2000. You will need to add any desired cfprocresult tags.

[cutie369]

Hi Bob, I am not sure what this T-SQL code is for? I was able to get my sp working with your help. Thanks, please give me some more information about the code you suggested that I run. Things are working nicely now. Thank you!

[JR__Bob__Dobbs-qSBHQ2]

The SQL code I provided will generate the cfstoredproc and cfprocparam tags for a given stored procedure.

[cutie369]

What a most useful peace of code? I have a question do you have any sample code using the MVC concept and Stored Procedures. I ordered the book that you suggested.

[JR__Bob__Dobbs-qSBHQ2]

I don't have any MVC samples, but I do recommend that that use CFCs to make your code re-useable.

I may be reading too much into your question but If you are interested in a framework for ColdFusion I've heard good things about Mach II http://www.mach-ii.com/ and FuseBox http://www.fusebox.org/ but haven't used either in production.

[cutie369]

Bob, thanks, I agree with you that CFC's are the way to go. I have been using them exclusively for the past year. I want to move towards doing object oriented applications and I was wondering if you had any examples of this doing stored procedures. Or not only you but anyone else who may be reading this. I have looked at MachII but it seems too difficult. I am not a huge fusebox fan either. Model Glue seems like the one I could wrap my arms around.