sql-server-2008 coldfusion coldfusion-9 temp-tables cfstoredproc

Coldfusion CFSTOREDPROC does not return a table

I am using Coldfusion 9 and SQL Server 2008. I'm trying to learn the platforms and really learn how to effectively leverage both platforms for a reporting solution. I've had some big challenges with stored procedures, or sprocs. All I want is a really simple answer to get my sprocs to the point where I can leverage them in CFML and get data to the people who need it.

I've done some research online and it appears that SQL Server 2008 takes advantage of temporary tables that are placed into RAM (@) or written to disk for a local user(#). My problem is that I can't seem to get the generated temp table to return data back to ColdFusion from the process call, Although when I run the portion of the SQL query that gets added to the temporary table in SQL Server it works like a charm. All I want to do is return a simple table to ColdFusion and ensure I am coding this in the most efficient manor possible.

I've tried: - Using the Return statement, but this seems to generate syntax errors with RAM tables (@). - Using Persistent Temp Tables (#) - Though various online forums seem to think by using this an application takes a big performance hit by doing this. (Everyone's a critic.)

My goals are: - To learn this process as well as I can to re-use it as much as I can - Learn the right way to do this so the query performs as well as it can. - Understand under what situations I use (@) and (#) temp tables in SPROCS and why. Some think that persistent temp tables (#) are better because it frees ram up for other sprocs fired during the compilation, while others think having the server go to the hard disk to write data is slower. - Understand why the syntax below doesn't work

Currently my CFML code looks like this:

<!--- ===========================================================================================================================
================================================= Page Resources ================================================================
=============================================================================================================================--->
<CFSTOREDPROC datasource="PoliticalDonationsDB" procedure=" sp_GetCurrentDonationCount_withDateRange" result="DONATIONCOUNT">
    <!--- In --->
    <cfprocparam cfsqltype="INT" dbvarname="SDate" value="2004">
    <cfprocparam cfsqltype="INT" dbvarname="EDate" value="2005">
    <!--- OUT --->
    <cfprocresult name="DonationCount">
</CFSTOREDPROC>

<!--- ===========================================================================================================================
================================================= Page Display ==================================================================
=============================================================================================================================--->

<HTML>
    <HEAD>
        <TITLE><CFOUTPUT>Title</CFOUTPUT></TITLE>
    </HEAD>

    <BODY>

        <div id="logo">
        </div><!--- End logo div --->

        <div id="currentRecords">
            <CFDUMP var="#VARIABLES#">
            <CFDUMP VAR="#DONATIONCOUNT#">
        </div><!---End currentRecords--->

        <div id="navigation">
            <ul>
                <li>Companies</li>
                    <ul>
                        <li></li>
                    </ul>
            </ul>
        </div><!--- End navigation div--->

        <div id="statisticsTab">
        </div> <!--- End Statistics div--->

    </BODY>
</HTML>

And My SQL Server code looks like this:

Use Politics 
GO

ALTER procedure sp_GetCurrentDonationCount_withDateRange
@SDate AS int,
@EDate AS int
AS
BEGIN
    --Create RAM Table
    DECLARE @DonationCountTable TABLE
    ( donationKey INT,
      CompanyKey INT,
      SenatorKey INT,
      donationAmount MONEY,
      donationDateFY INT
    )

    --Put the stuff into the RAM table
    INSERT INTO @DonationCountTable (donationKey, CompanyKey, SenatorKey, donationAmount, donationDateFY)
    SELECT * FROM PoliticalDontations
    WHERE donationDateFY BETWEEN @SDate AND @EDate
    ORDER BY donationDateFY, CompanyKey ASC

    --Get the stuff out of the RAM Table
    SELECT * FROM @DonationCountTable
END

Solution

You need this:

<cfprocparam cfsqltype="CF_SQL_INTEGER" value="2004">

DBVARNAME is deprecated:
http://livedocs.adobe.com/coldfusion/8/htmldocs/help.html?content=Tags_p-q_14.html

Here is a list of acceptable CFSQLTYPES

CF_SQL_BIGINT
CF_SQL_BIT
CF_SQL_BLOB
CF_SQL_CHAR
CF_SQL_CLOB
CF_SQL_DATE
CF_SQL_DECIMAL
CF_SQL_DOUBLE
CF_SQL_FLOAT
CF_SQL_IDSTAMP
CF_SQL_INTEGER
CF_SQL_LONGVARCHAR
CF_SQL_MONEY
CF_SQL_MONEY4
CF_SQL_NUMERIC
CF_SQL_REAL
CF_SQL_REFCURSOR
CF_SQL_SMALLINT
CF_SQL_TIME
CF_SQL_TIMESTAMP
CF_SQL_TINYINT
CF_SQL_VARCHAR