Skip to main content
A
Anonymous
November 15, 2007
Answered

Converting ColdFusion Code to TSQL

  • November 15, 2007
  • 1 reply
  • 1268 views
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.

This topic has been closed for replies.
Correct answer JR__Bob__Dobbs-qSBHQ2
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.



1 reply

J
JR__Bob__Dobbs-qSBHQ2
Inspiring
November 15, 2007
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
A
Anonymous
November 15, 2007
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.
J
JR__Bob__Dobbs-qSBHQ2Correct answer
Inspiring
November 15, 2007
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.



Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices