cfquery - tsql - set rowcount and cfqueryparam

[Guest]

I created a simple t-sql query inside <cfquery> tags. I used t-sql "Set RowCount" instead of "Top" to limit the rows returned to 1 row.  The query contained two <cfif> blocks to determine a value in the Where clause, and it also used  <cfqueryparam> tags to ensure right values/datatypes.

Although I received no error when I ran the CFML script,  I noticed that the "set rowcount" command did not work. If I remove the <cfqueryparam> tags, then the "Set Rowcount" command works.

My reason for posting is to see if anyone else has had this experience with "Set RowCount" and <cfqueryparam>.  I can find no documentation for this phenomenon.  Thanks in advance ....

Eli ...    

[Adam Cameron.]

Rather than kinda describe your code, can you pls post it here?

Cheers.

--

Adam

[Guest]

<cfquery name = "getOcid" datasource="myDB">

      set rowcount 1

      select distinct

      myID,

      description

      from myTable

      where 1=1

      <cfif url.source eq "main">

            and myID = <cfqueryparam value='#form.OCID#' cfsqltype="cf_sql_varchar">

      </cfif>

      <cfif url.source eq "xls">

            and myID = <cfqueryparam value='#url.OCID#' cfsqltype="cf_sql_varchar">

      </cfif>

      -- set rowcount 0

</cfquery>

<!---

Note: I commented out the final "set rowcount 0" - if this code is working properly, then any subsequent report

page you view during the same user/session should return only one row, until you run into another

"set rowcount 0"  statement;

The code above recognized the set rowcount 1 statement only after I replaced the cfqueryparams with

a simple variable (e.g.: and myID = '#form.OCID#');

--->