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
You need this:
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