May182007
ColdFusion: Stored Procedures For Beginners
General, ColdFusion, SQL
Comments (2)
So, you are a beginner in ColdFusion and you want to know the quick and dirty way to build a stored procedure and call it in ColdFusion.
Step One: Open SQL Server Enterprise Manager and browse to your database, you will find a section called "Stored Procedures", right click on it and select "New Stored Procedure".
Step Two: A window "Stored Procedure Properties" window will open where you will drop in your t-SQL code for running a query. Save it and you are good to go. Below you'll find the window and example code.
@firstname varchar(50)
AS
SELECT dbo.[User].firstName AS Firstname, dbo.[User].lastName AS Lastname, dbo.[User].email AS EmailAddress, dbo.Role.name AS Role,dbo.Organization.Name AS OrgName, dbo.Permission.name AS PermissionName
FROM dbo.RolePermission INNER LOOP JOIN
dbo.UserOrganization INNER LOOP JOIN
dbo.[User] ON dbo.UserOrganization.UserID = dbo.[User].userID INNER LOOP JOIN
dbo.UserOrganizationRole ON dbo.UserOrganization.UserOrganizationID = dbo.UserOrganizationRole.userorganizationid INNER LOOP JOIN
dbo.Role ON dbo.UserOrganizationRole.roleid = dbo.Role.roleId ON dbo.RolePermission.roleid = dbo.Role.roleId INNER LOOP JOIN
dbo.Organization ON dbo.UserOrganization.OrganizationID = dbo.Organization.OrganizationID INNER JOIN
dbo.Permission ON dbo.RolePermission.permissionid = dbo.Permission.permissionID
WHERE dbo.[User].firstName = @firstname
GO
Step Three: Open up a ColdFusion document and drop the following code in to call the stored procedure. You'll need the datasource to the database where the strored procedure lives, the stored procedure name, and a name you will call your result, also pass in any parameters that the stored procedure needs.
<cfstoredproc datasource="aDatasource" procedure="getStuff">
<cfprocresult name="myQueryResultSet" />
<cfprocparam type = "IN" CFSQLType="cf_sql_varchar" value="#myFirstname#" dbvarname="@firstname">
</cfstoredproc>
Dump your stored procedure result.
And just like that, you've got results. A stored procedure is really quite simple and very useful.
Why use stored procedures?
Many times, depending on what you are doing a stored procedure is much quicker. Everytime you write a query in ColdFusion, the SQL server takes it and says... "What would be the most efficient way to run this query?" It then runs it. Whereas with a stored procedure, the "Execution Plan" of how to run the query most efficiently is stored in the system so the next time you call the stored procedure, it doesn't have to evaluate and come up with an "Execution Plan."
The main reason for using them is speed. You'll have to evaluate for yourself based on your situation at hand, what is quicker for the task at hand. And no, I don't recommend using stored procedures for every little query, but for some of the bigger more complex ones... WHY NOT???
NOTE:
Microsoft SQL and Sybase among others use Transact SQL as the language to write a stored procedure. It is extremely robust and evolved and it may behoove you to learn some of the functionality available to you to perform more advanced operations (branching logic, looping over results, etc...).
An excellent reference book on this subject can be found here.
I will bookmark this page.
Kind regards,
J.Mihai
http://www.jmihai.ro
Glad to help!