Skip to main content
A
Anonymous
July 10, 2010
Question

cfquery - tsql - set rowcount and cfqueryparam

  • July 10, 2010
  • 1 reply
  • 1013 views

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 ...    

    This topic has been closed for replies.

    1 reply

    A
    Adam Cameron.
    Inspiring
    July 10, 2010

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

    Cheers.

    --

    Adam

    A
    Anonymous
    July 11, 2010

    <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#');

    --->

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