The July 2004 (Volume 6, issue 7) edition of CFDJ featured an article I authored entitled "Harnessing the Power of SQL Server Using Stored Procedures." In that article I covered the basics of building stored procedures and using them in ColdFusionMX using ColdFusion Components. This article will expand further upon that topic, delving deeper into the advanced capabilities of T-SQL and SQL Server.
In this article, I will demonstrate some techniques that I hope you can use in your own projects, and I will provide information and examples on the following topics:
About T-SQL
Structured Query Language (SQL) was developed by IBM in the mid-1970s. SQL is both an ANSI and an ISO standard. Transact-SQL (T-SQL) is a proprietary set of programming extensions from Sybase and Microsoft that add additional features to SQL. These features include transaction control, exception and error handling, row processing, and declared variables.
Conceptual Application Architecture
In the previous article, I discussed n-tier architecture. I am going to talk a bit more about this in order to get you thinking about what it means. The reason is that when using advanced T-SQL, you really have the ability to get kind of wild and crazy with how much business logic you push down into the data tier. Before you rush off to do this, I want to spend some time talking about application architecture.
With n-tier architecture, efforts are made to separate presentation logic from business logic. This kind of architecture is often logical in nature - meaning there isn't necessarily (although there could be) a physical separation of the layers. The separation is popular among developers for a number of reasons. Off the top of my head, these include:
Applying this architecture to your ColdFusion project means that:
An architectural consideration when designing an application involves deciding how much business logic to place in the data tier versus the business objects. A purist would say that all business rules should be completely encapsulated in the business objects tier leaving the data tier to simply serve up data. The reality is that there are advantages and disadvantages to each approach, making this decision even more difficult.
I'm not going into a full discussion on this topic, but I will mention what I consider to be important considerations. One is whether or not your application will be the exclusive "user" of the database. Keep in mind that there may be one or more additional applications, thin- or thick-client, that could potentially be developed now or in the future using the same stored procedures. By putting business logic into your stored procedures, you can effectively share that business logic between otherwise disparate applications.
Another important consideration when trying to figure out how much business logic to put into your stored procedures is performance. Certain capabilities may simply run faster in your application's business objects versus the data tier, or vice versa. If performance is your chief concern (and when is it not a concern?), then you will want to give this area some careful thought. My experience with ColdFusion and SQL Server is that unless I am dealing with string manipulation, SQL Server will generally outperform even my most tightly developed ColdFusion code (unless I've written some particularly bad T-SQL that is). Performance is as much an art as it is a science in my opinion. You can (and should) try to test proof of concept builds of key functional pieces of your application if time permits.
When working on .NET projects, I find that it is perhaps a bit easier to figure out where to put my business logic. With .NET, business rules can be encapsulated solely in the business objects and thin, thick, compact, or console-based .NET User Interfaces (UIs) can be plastered on top of them. In .NET, business objects are called class files. Building an n-tier application with .NET means that instead of baking business rules directly into your WinForm or ASP.NET code-behind, you create business objects (class files) to handle the heavy lifting. These class files can even be compiled into a stand-alone .dll file and then be referenced by any .NET application you might develop in the future. When you need the functionality that your business object provides in your application, you simply instantiate an instance of it in your WinForm or ASP.NET code-behind, and call whatever method(s) are needed from that instance of the object. As long as you use .NET for all of your UIs, those business objects can be shared across all of your .NET applications, regardless of the UI chosen for the project. Thus, for .NET projects at least, it really makes sense to keep your business rules in your business objects to the greatest extent possible.
With ColdFusion, we are dealing strictly with a UI for the Web. One way to make ColdFusion's business objects reusable is to expose them as Web services. This allows the ColdFusion business objects to be shared with non-ColdFusion applications that are capable of consuming Web services. If you go this route, make sure to consider the security of your Web services, as well as making your return types as generic as possible to ensure a high level of compatibility with consuming applications.
All right, enough of this discussion for now. I hope I've given you some food for thought on this topic. Let's move on to the core of this article - Advanced T-SQL.
Leveraging SQL Server
Microsoft SQL Server is a powerful relational database management system (RDBMS) that is, generally speaking, too often underutilized by ColdFusion developers. This article will not discuss how to create a stored procedure - please refer to the July 2004 article for that detail if necessary.
Using a Cursor
In the previous article, I provided a T-SQL code teaser of a stored procedure that uses a cursor. That code is reprinted here, but this time I'll step through it in detail and provide contextual information for its usage.
Cursor is an acronym that stands for a current set of records. As a ColdFusion developer you are no doubt familiar with the tag <cfloop>. Now imagine that you can perform a <cfloop> inside a record set inside of a stored procedure! Cursors are similar to while loops except instead of the while iteration happening in your ColdFusion middleware code, it can happen in your stored procedure.
The stored procedure that will be examined here is used in a custom shopping cart application that I wrote earlier this year (see Figure 2). As with all shopping carts, users visit the site and populate items into their shopping carts. tblStagingOrderProduct is the entity that stores the contents of users' shopping carts while they browse the site. As items are added or removed from the shopping cart, the contents of this table change. The field nchUUID in tblStagingOrderProduct is a UUID that is maintained in a session scope (via a session-based user object) to link the users' carts to their session.
When users determine that they wish to check out, they are first asked to log in if they have not already done so. This allows the intCustomerID to be set in the session-based user object. Next, users are prompted to enter payment and shipping information. This information is then submitted to a payment gateway. If the response from the payment gateway (based on the user's credit information) is okay, then the stored procedure spGS_Utl_ProcessOrder is executed.
spGS_Utl_ProcessOrder will create the new order in tblOrder and seed the tblOrderProduct table with all of the items in tblStagingOrderProduct for the nchUUID linked to the user. This means that only valid, approved orders are created in tblOrder and tblOrderProduct. The contents of tblStagingOrderProduct are periodically purged by another stored procedure that is scheduled to run on a daily basis by SQL Server DTS (Data Transformation Service - more on this later). This stored procedure removes any records whose dteTimestamp is >= 24 hours old, keeping tblStagingOrderProduct from filling up with potential orders not committed from more than a day ago.
A cursor is used in spGS_Utl_ProcessOrder to iterate over each item in tblStagingOrderProduct for the UUID. Each matching item is then inserted into tblOrderProduct.
The full T-SQL source listing is included for reference (see Listing 1). Let's break down each part of the stored procedure to see what is happening.
CREATE PROCEDURE spGS_Utl_ProcessOrder @nchUUID char(35), @intCustomerId int, @monAmountCharged money AS /* Purpose: rolls an authenticated order over from staging into non staging tables.*/ DECLARE @intOrderId int, @dteOrderDate datetime, @intProductId int, @intQuantity int -- set the order date using the SQL Server GETDATE() function SET @dteOrderDate = GETDATE()
Here, I am creating the stored procedure and defining three input parameters that the stored procedure will expect when it is executed. These fields are the UUID, the customer ID from the customer table, and the amount charged. In the production version of this stored procedure, there are additional input parameters, but I've simplified this example in order to make it easier to see what is happening.
Next, I declare four additional variables that are going to be used by the stored procedure later on. The order ID is a value that I will set when I prepare to roll the contents of the user's shopping cart over to a real order. The order date will be set by the SQL Server built-in function GETDATE(), which returns the system date. The product ID and the quantity are both used by the cursor, as we will see in a moment.
/*get the next available order ID, note use of output parameter. @intOrderID will hold the value that is returned by spCMS_Utl_NextNum */ EXEC spCMS_Utl_NextNum @intOrderId OUTPUT -- insert the order header into tblOrder INSERT INTO tblOrder ( intOrderId , intCustomerId , dteOrderDate , monAmountCharged) VALUES ( @intOrderId , @intCustomerId , @dteOrderDate , @monAmountCharged )
Next, I call out to another stored procedure, spCMS_Utl_NextNum, to get the next available OrderID. Note that I am specifying @intOrderID as an OUTPUT parameter. I will explain this in more detail later in this article. What you need to know is that when I execute spCMS_Utl_NextNum, @intOrderID is filled with the next available order ID.
After populating @intOrderID, I then append the order header to the order table.
Last, I set up a cursor in order to move the items out of the shopping cart and into the table that holds the items ordered by the user. Here is the T-SQL syntax for declaring a cursor:
DECLARE cursor_name CURSOR [LOCAL | GLOBAL] [FORWARD_ONLY | SCROLL] [STATIC | KEYSET | DYNAMIC | FAST_FORWARD] [READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] [TYPE_WARNING] FOR select_statement [FOR UPDATE [OF column_name [,...n]]]
You will definitely want to read up on this in SQL Server Books Online in order to understand what all the options mean. Cursors have performance implications; when you use them, you usually want to be working with a small number of records and use Forward-Only, Fast Forward, or Read Only cursors as these perform best. There are alternatives to cursors, including while loops, derived tables, and more. In my case, I decided to use a cursor because I was dealing with a small number of records.
/* use a cursor to insert all of the order items (children) into tblOrderProduct for the nchUUID See books online Transact-SQL under topic CURSOR for explanation of various options */ DECLARE csrRecord CURSOR LOCAL FORWARD_ONLY STATIC FOR SELECT intProductId, intQuantity FROM tblStagingOrderProduct WHERE nchUUID = @nchUUID OPEN csrRecord FETCH NEXT FROM csrRecord INTO @intProductId, @intQuantity -- see @@FETCH_STATUS in books online. Zero means that the Fetch was successful. WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO tblOrderProduct ( intOrderId , intProductId , intQuantity ) VALUES ( @intOrderId, @intProductId, @intQuantity ) FETCH NEXT FROM csrRecord INTO @intProductId, @intQuantity END CLOSE csrRecord DEALLOCATE csrRecord
Looking at the T-SQL code, note that I first declare the cursor and then immediately below that, create a record set by selecting the product ID and quantity from the table holding the user's shopping cart data.
FETCH NEXT FROM then takes the values for those variables in the current iteration and populates @intProductID and @intQuantity.
Next you see the statement "while @@FETCH_STATUS = 0." This essentially means "while there are still records in the record set that do this", effectively creating the construct needed to end the iteration when there are no more records.
Now I go ahead and insert the product and quantity for the current iteration into the table that holds the ordered items for the order. Since I have the order ID, I insert that as the foreign key into that table.
Last, it is very important to close the cursor and DEALLOCATE it in order to free up the resources used by the cursor.
The end result is that I now have a single stored procedure that I call, pass three input parameters, and have the user's order rolled over from a nonorder to a validated order. Instead of writing scores of lines of CFML, I now can write one simple <CFFUNCTION> in my Order.CFC file to roll over orders (See Listing 1).
Nesting Stored Procedures
Nesting stored procedures simply means that you call out to one or more additional stored procedures from the one you are in. For example, imagine that you execute stored procedure A. During execution, stored procedure A executes stored procedure B to retrieve some data that stored procedure A then uses to complete its task.
An example of this is depicted in the stored procedure spGS_Utl_ProcessOrder that you read about in the Cursors section. This procedure contains a nested stored procedure, spGS_Utl_NextNum. That line reads: "EXEC spGS_Utl_NextNum @intOrderID OUTPUT." As you can see, one stored procedure can easily call another, thereby reusing functionality instead of rewriting it each time from scratch.
As a data architect, this should tell you something very important when modeling your database and developing your data objects - always consider reusability when developing your database. Truly great programmers are just plain lazy, and for good reason.
Here is the T-SQL for the spGS_Utl_NextNum stored procedure:
CREATE PROCEDURE spGS_Utl_NextNum @intOrderID int OUTPUT AS DECLARE @newVal int -- get nextNum value SELECT @intOrderID = intNextNum FROM tblNextNum -- increment the nextNum value SET @newVal = @intOrderID + 1 UPDATE tblNextNum SET intNextNum = @newVal -- return the nextNum value SELECT @intOrderID
Output Parameters
Output parameters can be used with nested stored procedures as shown in spGS_Utl_ProcessOrder, or can be used to return a value to ColdFusion (rather than returning a complex value such as a record set). I have found this to be most valuable when returning the next primary key value. spGS_App_Customer depicts an example of appending a new customer record to tblCustomer and providing the new intCustomerID as an output parameter.
CREATE PROCEDURE spGS_App_Customer @vchFName varchar(30), @vchLName varchar(60), @intCustomerID int OUTPUT AS INSERT INTO tblCustomer ( vchFName, vchLName ) VALUES ( @vchFName, @vchLName ) SELECT @intCustomerID = @@IDENTITY
To call this stored procedure from a ColdFusion business object, the following code would be used:
<cfstoredproc procedure="spGS_App_Customer" datasource="#this.DSN#"> <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@vchFName" value="#arguments.vchFName#" null="No"> <cfprocparam type="In" cfsqltype="CF_SQL_VARCHAR" dbvarname="@vchLName" value="#arguments.vchLName#" null="No"> <cfprocparam type="Out" cfsqltype="CF_SQL_INTEGER" variable="intCustomerID" dbvarname="@intCustomerID" null="No"> </cfstoredproc>
Note how the third cfprocparam statement specifies type = "Out". What happens here is the next primary key value from tblCustomer is then assigned to a ColdFusion variable named intCustomerID. It's up to you what you want to do with that value - maybe you just want to return it from the cffunction, or maybe there is some other work that needs to be done before returning a value. The point is that a simple value is returned from the stored procedure call rather than a query being returned.
User Defined Functions and String Manipulation
User defined functions in SQL Server can be used for a variety of purposes. Functions in SQL Server work in somewhat the same way as functions in ColdFusion - they receive one or more arguments and return some value. Just as in ColdFusion, user defined functions in SQL Server are reusable and this is a key reason to use them. Because we are dealing with data here, user defined functions can initiate SELECT statements to retrieve data from any table in the database; however, a user defined function cannot specify any operation that modifies data in any way. For example, you cannot use a user defined function to INSERT or UPDATE data.
User defined functions can be created in SQL Query Analyzer or in SQL Enterprise Manager. If you use SQL Enterprise Manager, right-click on User Defined Functions and select New User Defined Function. The syntax for a user defined function will be displayed as:
CREATE FUNCTION [OWNER].[FUNCTION NAME] (PARAMETER LIST) RETURNS (return_type_spec) AS BEGIN (FUNCTION BODY) END
If you use SQL Query Analyzer, you won't be given any clues about the syntax - you're expected to know it. Figure 3 is a screenshot from SQL Query Analyzer showing some user defined functions.
I'll show you a simple user defined function called fcnGetAppUserRoleDesc. This function returns a string containing the word "Admin" or "User" denoting the permission level of a user. Rather than writing conditional logic in ColdFusion for this purpose, I elected to push that logic down to the data tier in this example. The end result is that the word "Admin" or "User" is part of the record set that spGS_Sel_User returns. I elected to put this logic into a user defined function instead of using the T-SQL CASE statement because I thought there might be other stored procedures that would want to reuse the functionality of this user defined function (see Listing 2).Here are some cfstoredproc statements for interacting with spGS_Sel_User:
<!--- calling spGS_Sel_User this way will return all users from tblAppUser ---> <cfstoredproc procedure="spGS_Sel_User" datasource="#this.DSN#"> <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@intAppUserID" null="Yes"> <cfprocresult name="RS1"> </cfstoredproc> <!--- calling spGS_Sel_User this way will return properties for user ID number 5 ---> <cfstoredproc procedure="spGS_Sel_User" datasource="#this.DSN#"> <cfprocparam type="In" cfsqltype="CF_SQL_INTEGER" dbvarname="@intAppUserID" value="5" null="No"> <cfprocresult name="RS1"> </cfstoredproc>
User defined functions are a great place for handling string manipulation. In several cases, I've used user defined functions to provide formatted HTML that I then return in a recordset. Here is some T-SQL that I pulled out of one of my user defined functions that illustrates this technique:
DECLARE @vchSubHeadCode varchar(500) SET @vchSubHeadCode = '<a href="multitab.cfm?intStudentCourseId=' + CAST(@intStudentCourseId AS varchar(10)) + '&intGradeLevelId=' + CAST(@intGradeLevelId AS char(1)) + '&StudentId=' + CAST(@intStudentId AS varchar(10)) + '" onClick="fcnSubmit();">Modify</a>' + SPACE(2)
In this code snippet you can see that I am creating the first part of an HREF statement that has some query string arguments. The parameters @intStudentCourseId, @intGradeLevelId and @intStudentId are integer data types that are arguments of the user defined function that this code snippet is pulled from. In order to use those values in a concatenated string, I need to use the SQL Server built-in function CAST() to cast the integer data type to a string data type, such as char or varchar. The SPACE() built-in function creates white space in the string. Also note that the concatenation operator in T-SQL is the plus (+) symbol, not the amperstand (&) that is used in CFML. Also remember that SQL Server is a dog at string concatenation, so be smart about when and where you do string concatenation. There have been a number of times in my experience when returning preformatted text from the stored procedure made sense and provided a more elegant architecture. For this reason, I wanted to share this technique with you so that you could add it to your arsenal of T-SQL knowledge.
Specifying Default Values for Input Parameters
Normally when defining an input parameter for a stored procedure, simply type an @ symbol followed by your variable name, followed by its data type. If that is all you do, then you have created a required input parameter for your stored procedure. However, what if you want to make the inclusion of a value optional and set a default value for an input parameter if that value isn't specified when the stored procedure is executed?
spGS_Sel_User depicts an optional input parameter:
@intAppUserId int = NULL
In effect, this means that @intAppUserId is not a required argument for the stored procedure. If the user ID is not passed in, then it defaults to NULL. It would be possible to specify an integer value as the default value for @intAppUserId if NULL wasn't what you wanted to do, for example, @intAppUserID int = 5, where 5 might be a demo account, for example.
Linked Servers
Sometimes you may have more than one SQL Server that you need to access in order to retrieve data for your project. Or, perhaps you have an Oracle database in addition to the SQL Server database that you need to communicate with for your project requirements. Once two servers are linked, T-SQL statements can be written on SQL Server A to work against data objects on SQL Server B via OLEDB. This is an incredibly powerful feature of SQL Server and something that you should definitely know about in case you run into a situation where you need to use this knowledge. I'll give you an example of one of my own experiences.
I recently worked on a project for a company that used SQL Server for their Web applications and Oracle for their in-house thick-client applications. The project required that I pull large amounts of data from the Oracle database, cleanse the data (due to human data input error), make it searchable via Verity, and expose it on the Web. Because Oracle was used to drive their operational systems, anything I did with Oracle needed to be low touch. For this same reason, my access to the Oracle server was just high enough to access the tables I needed to retrieve data from.
Initially, I tried pulling the data out of Oracle using a ColdFusion ad hoc query because I did not have sufficient rights on the Oracle server to create PL/SQL stored procedures. Once I had the record set in ColdFusion, I used CF to cleanse the data through multiple iterations and then insert the cleansed data into SQL Server. Last, I would run some CFM files that would index the Verity collections. What I learned after I had built this elaborate mousetrap was that it was very slow. Worse, since the client was going to be adding even more data to Oracle over time, my architecture would not scale. What to do?
Searching for a better architecture, I learned that it would be possible to link to the Oracle RDBMS server directly from SQL Server. By linking the SQL Server to the Oracle Server I could effectively use T-SQL instead of PL/SQL to access data on the Oracle Server. Moreover, I could create stored procedures on the SQL Server to do this job and then schedule the job to run in the middle of the night with Data Transformation Services. To put this into an easily understandable perspective, the mousetrap architecture took about an hour to complete versus the linked server architecture, which took under 5 minutes to complete. It may have taken so long to run because I am not a strong PL/SQL developer; however, there were definitely some other factors in play:
To link an Oracle server to an SQL Server, follow these steps:
USE master GO -- create the linked server EXEC sp_addlinkedserver 'ORACLE_DB_ALIAS', 'Oracle', 'MSDAORA', 'ORACLE' GO -- set login credentials for the linked server EXEC sp_addlinkedsrvlogin 'ORACLE_DB_ALIAS', 'FALSE', NULL, 'Oracle_username', 'Oracle_password'.
Now, you can write a T-SQL statement to pull data from the Oracle Server. In this example, what I am doing is joining two tables on the Oracle Server to retrieve a record set. This stored procedure uses a subquery in the WHERE clause to further refine the resulting record set. This stored procedure is slightly modified to simplify it from its production form. I am also using table aliases for the table names as the four-part names for the tables are quite long and cumbersome to type (see Listing 3).
The two periods after ORACLESERVER in the FROM and WHERE clauses of the example are not typos. Calling out to a linked server requires the use of a four-part name which is: linked_server_name.catalog.schema.object_name (see Table 1).
In my case, there is no catalog, but a placeholder for the nonexistent catalog using the four-part name notation is required.
Date Functions
If you have become familiar with the Date functions available to you in ColdFusion, you should have no trouble picking up these useful date functions. These functions are very useful when working with datetime fields; you should strongly consider using them in your stored procedures instead of trying to manipulate dates in your ColdFusion middleware code wherever possible (see Table 2).
Note that when combined with a cursor, DATEADD() can be used with a stored procedure to return one or more record sets containing the months of any given year for the creation of an online calendar, for example.
Using DTS to Create and Schedule a Package
Here is how Microsoft defines DTS:
A DTS package is an organized collection of connections, DTS tasks, DTS transformations, and workflow constraints assembled either with a DTS tool or programmatically and saved to Microsoft SQL Server, SQL Server 2000 Meta Data Services, a structured storage file, or a Microsoft Visual Basic file.
Each package contains one or more steps that are executed sequentially or in parallel when the package is run. When executed, the package connects to the correct data sources, copies data and database objects, transforms data, and notifies other users or processes of events. Packages can be edited, password protected, scheduled for execution, and retrieved by version.
DTS is definitely worth reading about in the SQL Server Books Online and is arguably one of the more powerful aspects of SQL Server. I have heard that the upcoming release of SQL Server will allow DTS services to be written in .NET.
I'm going to show you how to create a simple DTS service using the DTS Designer included with SQL Enterprise Manager and then show you how to schedule it (see Figure 4). The shortest interval for a DTS Package to be scheduled is 60 seconds.
To create a DTS Package using the DTS Designer, follow these steps:
exec spMyProc
Last, to schedule the DTS Service, right-click the service you just created and choose Schedule Package. Set the schedule to repeat as often as required. Please note that you will need to have the SQL Server Agent service running on the database server to use this feature.
Conclusion
In this article I have demonstrated several advanced T-SQL techniques. Many of the topics covered could be stand-alone articles in their own right. Additional topics that could be covered in future articles include transactions, temporary tables, table variables, and handling multiple record sets. If you would like to see additional coverage of these topics, or if there are additional topics beyond these that you would like to see covered, please e-mail me and let me know. Also, please continue your learning by spending some time with SQL Server Books Online.