Need assistance passing a CF session variable to a SQL stored procedure

(The SP is already done and works flawlessly in CQL) I meant SQL - Sorry.

1) How do I pass the variable from a cfm page?

Call the procedure with cfstoredproc and pass it in as a parameter.

        <cfstoredproc procedure="yourProcedureName" datasource="....">
             <cfprocparam type="IN" value="#session.yourVariable#"
                           cfsqltype="(your type)">
             <cfprocresult  name="theProcResult">
         </cfstoredproc>

              2) How do I display the stored procedure's output?

You can do it dynamically just like w/a cfquery.  Use cfprocresult to capture the output in a query. Then loop through the query columnList to display the results dynamically
         
          <!--- create array of column names --->
         <cfset colArray= listToArray(theProcResult.columnList)>
         <table>
         <cfoutput query="theProcResult">
          <tr>
                  <!--- Requires CF8+ --->
                 <cfloop array="#colArray#" index="colName">
                     <td>#theProcResult[colName][currentRow]#</td>
                 </cfloop>
           </tr>
        </cfoutput>
         </table>

@_agx_,

We're trying to phase out CF and so we have not spent the $ to continue to upgrade it. We don't have CF8, and if I'm not mistaken, probably have CF6. "Array" is not on the list of delimiters for my cfloop. What else can I try in it's place?

Then use either a "list" loop

              <cfloop list="#theProcResult.columnList#" index="colName">
                     <td>#theProcResult[colName][currentRow]#</td>
                 </cfloop>

... or a from/to loop:
              <cfloop from="1" to="#arrayLen(colArray)#" index="x">
                     <td>#theProcResult[colArray[x]][currentRow]#</td>
                 </cfloop>

                > We're trying to phase out CF and so we have not spent
                > the $ to continue to upgrade it.

If you have existing apps that would be too expensive to rewrite, might look into the free alternative cfml engines like Railo or OpenBD.  You'd get the benefits of the newer features without the cost of upgrades.

@_agx_,

The variable is being passed along just fine. I am now just having issues with the display portion.

I did a cfdump and it did bring back all the info I require, but it isn't in a format I can use.

@_agx_,

I'll try your list loop idea.

> but it isn't in a format I can use.

How so?

@ _agx_,

CFDUMP:
Although the output has all the details I require the columns are not in the right sequence. I need the left column the be the userid column (which is somewhere in the middle column at this point) and the rest of the info from the stored procedure can follow horizontally.

If I could format this the way I need to (color, width, height etc.) the output would be perfect.

LOOP LIST:
Same as above, plus the loop list idea has no table header info.

---------------------------------------

If I can just format these to my liking I think we can call this project good.

Yeah, columnList is annoyingly always alphabetical.  (I wonder who thought that was a good idea).  

I don't know if CF6 supports it, but try dumping #getMetaData(theProcResult)#.  It should return an array of column props in the sql order: column name, type, ...  If not, try this function from cflib http://cflib.org/udf/getQueryColumns.  Hopefully one of them should work under 6...

Let me know which one works, then we can work on column headers.

I can't build an HTML table and hardcode column names as they may
        change over time
         ...
         If I could format this the way I need to (color, width, height etc.) the
         output would be perfect.

Well the html settings have to come to come from somewhere ;-) Unless you're generating them dynamically inside the proc - you must hard code. ie Set up a structure with the settings to handle columns you DO know about. The rest will just fall through to the defaults.

             <cfset prop.columnA.width = 100>
             <cfset prop.columnA.color = "ff0000">
             <cfset prop.columnB.width = 75>
             <cfset prop.columnB.color = "ffff00">
              ....

              <cfoutput query="...">
                    <cfloop list="#columnNames#" name="colName">
                          <cfif structKeyExists(prop, colName)>
                               <td bgColor="#prop[colName].color#" ... >
                          <cfelse>
                               ... column not found. use defaults ....
                          </cfif>
                    </cfloop>
              </cfoutput>

@_agx_,

Thanks for working on solutions for me into the wee hours. Upon returning to work I was pleasantly greeted by this plethora of responses.

To recap:
list loop and from-to loop - both display items from the db. - formatting and column order still required.

#getMetaData(theProcResult)# - surprisingly, this shows me that items #1 and #2 (Columns titled "Fullname" and "UserID") are the first ones that are returned by the query, yet cfdump, list loop and from-to loop all show both these columns scattered throughout the output (?). Fullname is an amalgam of columns titled Firstname and Lastname. I'm wondering if I sneakily retitle these two columns as 1Fullname and 2RecID in the stored procedure that they'll be displayed in the right order (0-9, A-Z)?

@_agx_,

I am going to have to think about changing my online persona handle to "LoTech" ;) . I changed the column names to aaFullname and aaUserID, and I'll be damned if that didn't handle that issue beautifully. Now for the table headers etc.

>  I'm wondering if I sneakily retitle these two columns as 1Fullname
     > and 2RecID in the stored procedure that they'll be displayed in the right
     > order (0-9, A-Z)?

Smart thought.  That should work. But as long as getMetaData works in MX you don't need it  :)  You just have to remember cfdump and columnList use alpha order and getMetaData another.  For this scenario, getMetaData's order will do nicely.

You, Sir, are "The Man!!! (no offense if you're female - insert appropriate title and salutations here to keep me in your good graces).

I have completed the project and have you to thank for it. On to other projects...cheers!

Thanks. Glad I could help knock this item off your to-do list :)