I recently attended a ColdFusion certification course. One of the things I learned in the course was the use of Stored Procedures(SP). SP are a great way to move your SQL statements out of your code and allows for code re-use. It also takes the strain off your ColdFusion server and forces your SQL server to crunch all the calculations/numbers (which is what it is intended for). This practice will help speed up your process time for your page.
One of the features of the cfstoredproc command is the cfprocresult command. The cfprocresult returns the resultset of your stored procedure as a query. If you have more than one resultset, the cfprocresult has an attribute to set which resultset you want.
For example, you create a stored procedure for finding the employees who are male or female in your organization. The stored procedure might look something like this:
BEGIN
SET NOCOUNT ON;
--Get Males
SELECT FirstName, LastName
FROM TableEmployee
WHERE Gender = 'M'
--Get Females
SELECT FirstName, LastName
FROM TableEmployee
WHERE Gender = 'F'
END
Within your code on your page, you can call these two results like this:
<cfstoredproc procedure="spEmployeesByGender" datasource="your_datasource">
<cfprocresult name="Males">
<cfprocresult name="Females" resultset="2">
</cfstoredproc>
There is no need to put a resultset on the first cfprocresult. However, if you only wanted to return the Females in this example, you must include the resultset attribute.
You can also pass variables to your stored procedure using the cfprocparam attribute of the cfstoredproc tag. Here is an example of the cfstoredproc tag with cfprocparam and cfprocresult.
<cfstoredproc procedure="spEmployeesByGender" datasource="your_datasource">
<cfprocparam cfsqltype="CF_SQL_VARCHAR" value="M"> <!--- Send M for Males --->
<cfprocparam cfsqltype="CF_SQL_VARCHAR" value="F"> <!--- Send F for Females --->
<cfprocresult name="MaleEmployees">
<cfprocresult name="FemaleEmployees" resultset="2">
</cfstoredproc>
Now you can define vairables within your stored procedure to pass to your query statements.
@MyMaleGender VARCHAR(1), @MyFemaleGender VARCHAR(1)
BEGIN
SET NOCOUNT ON;
--Get Males
SELECT FirstName, LastName
FROM TableEmployee
WHERE Gender = @MyMaleGender
--Get Females
SELECT FirstName, LastName
FROM TableEmployee
WHERE Gender = @MyFemaleGender
END
SELECT firstname, lastname
FROM TableEmployee
WHERE roleID IN ( 1, 2, 3, 4...)
I have no idea how you would convert that part in the parentheses to a SP parameter.
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
<cfstoredproc procedure="getAccoutnList" datasource="zzCL" result="storeproc">
<cfprocresult name="result" >
<cfprocparam cfsqltype="CF_SQL_VARCHAR" maxlength="100" value="1,2,3">
</cfstoredproc>
I agree with Paul here. If you already have a CF ValueList, then you can pass it to the SP in the cfprocparam tag. Just make sure you use ## around the list name.
In your SP, you will need to declare a variable to hold the list (i.e @MyList) then the WHERE clause would be:
WHERE roleID IN @MyList
Good luck.
http://www.salescheapbeatsbydre.com
http://www.monsterbeatsbydre-shop.com
http://www.northfacehonsale.org/