Transact-SQL

In Chapter 8, I described the general structure of the SQL commands that allows client software to select, insert, update, and delete records in a SQL Server table. Of course, Transact-SQL (T-SQL)the SQL dialect included with Microsoft SQL Serversupports that syntax. However, those simple statements barely scratch the surface of this rich programming environment.

Let's review briefly. Selecting data from a SQL data source uses a command in this form:

 SELECT<  fieldlist  >     FROM <  table name  >     WHERE <  condition  >     ORDER BY <  sort fields  > 

Of course, this command has many options, and not all the clauses of the statement are required. For instance, SELECT * FROM tblPatients is a perfectly valid TSQL select statement. SELECT TOP 5 * FROM tblPatients ORDER BY accountBalance is also a valid T-SQL statement. We use the TOP keyword to specify a limit to the number of records that should be returned. UPDATE , INSERT , and DELETE statements also use the same kind of regular syntax.

T-SQL also supports a limited procedural language. This includes support for IF , ELSE , WHILE , and CASE statements. With these procedural statements, T-SQL goes far beyond the limits of non-server-based databases. T-SQL can be leveraged to support server-based development with three general features:

• Triggers . These are statements that are triggered by an event on the server.
• Stored procedures . These are sets of procedures that are precompiled and stored on the server. Stored procedures can perform actions or return record sets.
• Extended stored procedures . These are somewhat similar to stored procedures, but rather than using T-SQL code, extended stored procedures are created in C or C++ and compiled into DLLs in a specific format.

These three components of SQL Server allow you to create applications that meet all of the requirements listed in the scenarios mentioned earlier in the chapter. Before discussing those scenarios in detail, let's look at what each of these three features do.

Triggers

Triggers are bits of T-SQL code that are fired off whenever a prescribed event takes place. SQL Server supports triggers on insert, update, and delete operations. Triggers can perform many functions. One of the most common is the enforcement of relational integrity. Triggers are fired after the operation that triggered them has taken place, but they have the ability to roll back the operation. Views are sometimes used to present users or client applications with restricted data from a single table or data from multiple tables joined together. Triggers are not fired on changes to a view, but if the underlying table that is part of a view has a trigger installed on the operation taking place via the view, the trigger on the table is fired.

Triggers can also perform other operations on the table that fired the trigger or on other tables. One other common use of triggers is to maintain a totals field in a header record. For instance, if triggers exist on the inserts, updates, or deletions of an order item table, those triggers could maintain a total stored in an order header table. From a purely relational standpoint this is generally not considered good form, but it is often a solution when you require instant, current information about order balances. Of course, an invoice built from such a system must add the order lines to ensure that the total is correct to the penny. However, for ad hoc reporting and other purposes, such a total might be invaluable. Triggers ensure that the totals stored will be accurate virtually all the time.

The T-SQL code in Listing 13-1 gives you an idea of what a trigger can do. The purpose of this trigger is to create a specialized transaction record whenever a new record is inserted into the target table.

Listing 13-1

 CREATE TRIGGER EXC_CHARGE_INSERT ON dbo.EXC_CHARGE_CODE FOR INSERT AS DECLARE @ID int,     @ChargeCode varchar(20),     @ChargeQuantity int,     @ServiceDate dateTime,     @PatientAccount float,     @UsrID varchar(255),     @OverrideDescription varchar(50),     @ExceptionDateID int,     @deptCode char(10),     @timeNow datetime,     @ExceptionDate datetime,     @EnteringArea char(7),     @EnteringAreaID char(10) SELECT @ID=ID, @ChargeCode=Charge_Code,     @ChargeQuantity=Convert(int, CHARGE_QUANTITY),     @ServiceDate=Convert(datetime, CHARGE_SERVICE_DATE),     @PatientAccount=PATIENT_ACCOUNT_NUMBER,     @exceptionDateID=PA_PROCESSING_EXCEPTION_DATE_I,     @UsrID=Usr_ID,     @OverrideDescription=Convert(varchar(50),         OVERRIDE_DESCRIPTION),     @EnteringAreaID=ENTERING_AREA FROM Inserted exec MySP_GetDeptCode @ChargeCode, @deptCode OUTPUT exec MySP_GetEnteringArea @EnteringAreaID, @EnteringArea OUTPUT     INSERT INTO tblExceptionsSummary         (Type, TypeFileID, ChargeCode,         ChargeQuantity,ServiceDate,         PatientAccount, UsrID,         OverrideDescription, Dept,         ExceptionDateID, DeptCode,         EnteringArea)     VALUES('CHARGE', @ID, @ChargeCode,         @ChargeQuantity,         @ServiceDate,@PatientAccount,         @UsrID,@OverrideDescription,         SUBSTRING(@ChargeCode, 1, 3),         @exceptionDateID,         @DeptCode,@EnteringArea) GO 

The trigger in Listing 13-1 shows many of the features that make T-SQL a suitable development environment for enhancing both client and server applications. Briefly, the purpose of the trigger is to get several fields from the inserted record, retrieve some data from two other files, and create a new record in another table. In doing so, the trigger illustrates the strengths of T-SQL. The first line actually creates the stored procedure:

 CREATE TRIGGER EXC_CHARGE_INSERT ON dbo.EXC_CHARGE_CODE FOR INSERT AS 

This is the syntax for creating any stored procedure. Of course, the trigger name, table name, and type of operation that will fire this trigger (in this case, INSERT ) will vary.

The first strength of T-SQL highlighted by the trigger is the broad support for many different types within the language. Of course there are integers, but there are also char , varchar , and dateTime types. Variables start with a leading "at" sign (@). While none are used here, there are system variables that start with "@@".

Once the variables are declared, the trigger uses a SELECT statement to get the fields from the record that is being inserted available as fields in the Inserted pseudotable into the variables just declared. Next the trigger calls two stored procedures.

 exec MySP_GetDeptCode @ChargeCode, @deptCode OUTPUT exec MySP_GetEnteringArea @EnteringAreaID, @EnteringArea OUTPUT 

These two lines execute two stored procedures. (I'll cover stored procedures in detail in the next section.) What's important about this code is how the variables are used. Each of these stored procedures, MySP_GetDeptCode and MySP_GetEnteringArea , expects two parameters. The first parameter in each case is one of the variables just retrieved from the Inserted pseudotable. The second parameter for each of the stored procedures is marked as an OUTPUT parameter, meaning that a value will be returned in those variables.

Finally, the trigger inserts a record into another table, taking the values from the inserted record and those retrieved using stored procedures. In many cases a trigger for this type of operation could be performed using a compound statement that feeds the INSERT statement with values from a SELECT statement. This was not possible in the example trigger because some exotic field type transformations were required. Thankfully, T-SQL allowed the required operations to take place.

One complication that often occurs when we work within the SQL language is type incompatibility. T-SQL provides a convenient function named CONVERT that converts variables from one type to another:

 CONVERT (data_type[(length)], expression [, style]) 

CONVERT accepts as parameters the data type and the expression to convert and, optionally, the style. This optional parameter is used for formatting when converting date/time types into strings. There is also a CAST function that performs a similar operation. Listing 13-1 shows several examples of CONVERT .

SUBSTRING is another function built into T-SQL:

 SUBSTRING(expression, start, length) 

SUBSTRING accepts three parameters. The first is expression , which should evaluate to a character string, binary string, text, image, or column. The return value is length bytes of expression starting at start . This function is used in Listing 13-1 to allow part of a field to be saved into a smaller field.

One advantage of triggers is their ability to offload required functionality from the client to the server. Recall the first scenario mentioned above.

You must design a system that uses Web clients for inquiry and simple data entry and also uses a Microsoft Visual Basic client for more sophisticated data entry and reporting. Both systems can insert records into the database. Inserting a record into one table should be accompanied by insertion of several records into another table.

How can triggers help you in this scenario? A common challenge of developing multiple clients to act on a single database is ensuring that each client behaves the same way for the same operation.

For example, I created a document-tracking system that allowed documents to be added from either a traditional fat client program or from a Web application. When a document was added to the tracking system, a series of operations needed to take place, depending on the document's attributes. One of those operations involved routing the documents to responsible parties. This required looking up document-responsibility information from a table and then creating routing records in another table. One of my concerns was ensuring that no matter which way the documents were entered they would create the same routing records. This is problematic even during initial implementation, but virtually impossible during updates, which might not always have the two types of client applications in synch.

Triggers provided me with a solution by moving functionality away from the clients to the server. The trigger was fired for all clients. Even if a third client came on board perhaps a Microsoft Visual C++ application the operation performed by the stored procedure would work exactly as it did for the existing clients.

Triggers can also help in situations in which totals need to be maintained, such as the second scenario described above.

You need to create an order entry system that will notify a system administrator whenever a customer's outstanding balance exceeds predefined limits. You would also like to have quick and easy reporting of customer balances without having to read through the entire database.

Relational design dictates that totals should not be carried in records in which related records would allow calculation of the totals. This is a good rule, and one that I always followed before the advent of reliable systems with available triggers. While I still seldom violate that rule, I find that trigger-maintained totals can be a lifesaver for some situations, such as the scenario mentioned above. Properly written triggers allow us to maintain totals with reasonable reliability. They also have a 0 default value for the fields that contain the totals. For huge databases that require up-to-the-minute information about totals, triggers can be crucial.

Stored Procedures

Stored procedures are another way to off-load processing and business logic to the server. In the example I just mentioned, where a trigger maintains totals, a stored procedure could allow standardized logic to calculate the totals no matter where they will be used or displayed. Any bit of business logic that needs to be used by multiple applications is a good candidate for stored procedures.

Listing 13-1 used two stored procedures. One of the stored procedures was named MySP_GetEnteringArea . Its source is shown in Listing 13-2:

Listing 13-2

 CREATE PROCEDURE MySP_GetEnteringArea     @EnteringAreaID char(10),     @EnteringArea char(7) OUTPUT AS     SELECT @EnteringArea = CHARGE_ENTERING_AREA.code     FROM CHARGE_ENTERING_AREA     WHERE CHARGE_ENTERING_AREA_ID =         CONVERT(float, @EnteringAreaID) 

This simple stored procedure does little more than call a SELECT statement and return a character string. The syntax for creating a stored procedure is similar to the syntax for creating a trigger. Why would such a simple SELECT statement not be called directly from the trigger? There are two reasons for this.

The first reason is that the function performed by this example stored procedure is a common requirement throughout the system I took it from. Placing this function in a stored procedure allows any changes required when looking up an entering area to be handled in the stored procedure. The importance of being able to hide the implementation was brought home to me when I recently needed to change the field type of CHARGE_ENTERING_AREA_ID in Listing 13-2. Dozens of other stored procedures and triggers used this stored procedure to get the entering area, passing in the EnteringAreaID as a 10-byte character string. A new release of the vendor's product changed CHARGE_ENTERING_AREA_ID 's type to a float field. Because the logic to return the entering area was encapsulated in this stored procedure, the stored procedure had to change but little else did. In other situations, more than just a data type might change. Underlying business rules can be encoded and enforced in stored procedures. When these business rules change, as they often do, the stored procedure can change without requiring widespread changes throughout a SQL Server application.

The second reason is that stored procedures can be faster than calling SELECT statements scattered throughout code on the server or in the client application. Inside Microsoft SQL Server 7.0 by Ron Soukup and Kalen Delaney (Microsoft Press, 1999) details the reasons why stored procedures offer a performance advantage. The advantage comes primarily from the fact that stored procedures are checked for syntax and compiled only the first time they are run.

Stored procedures provided with SQL Server conventionally use sp_ as the first three characters of their names. In Listings 13-1 and 13-2, I prefixed my stored procedures with the somewhat unoriginal mySP_ . Using a standard scheme to name your stored procedures is entirely optional. In some cases even the names of stored procedures that come with SQL Server are less than perfect. In at least one situation, a procedure beginning with sp_ is not in fact a stored procedure but an extended stored procedure (covered in the next section). For multiprogrammer projects in which many programmers will be creating stored procedures, some naming conventions should be implemented to ensure consistency of stored procedure names.

Extended Stored Procedures

As we have seen, T-SQL is a powerful language that is quite capable when manipulating databases. Unfortunately, not every object is a database. Devices and even other applications might require attention. SQL Server provides one more way to extend itself: extended stored procedures.

Extended stored procedures are created using any language capable of creating a DLL. Since stored procedures are in DLLs, you aren't required to create a new process when you call them. Rather, SQL Server loads the DLL into its own address space, and one of SQL Server's threads runs the extended stored procedure function. The implications of this structure should be obvious: Be sure that the function you intend to use as an extended stored procedure can handle exceptions and can follow all the other rules for multithreaded applications. One more important rule is to avoid changing anything related to the global state of the process. These changes affect not only your extended stored procedure, but SQL Server as well. While in theory an extended stored procedure could do all sorts of damage to SQL Server, in practice such damage seldom occurs.

Remember the third scenario mentioned at the beginning of the chapter?

You need to create a system that will access the e-mail system, but not all client machines will have the e-mail client installed. Nor, for that matter, will all users have their own e-mail accounts.

The extended stored procedures that come standard with SQL Server can solve this problem. As I promised during the MAPI discussion in Chapter 7, there is a way to allow SQL Server to perform some simple e-mail operations. The machine on which SQL Server runs must meet two requirements. First the machine must have a valid MAPI profile. Second the administrator must configure SQL Mail on the server.

Figure 13-1 shows SQL Server Enterprise Manager with the Support Services folder open. If you right-click on SQL Mail and select Properties, the dialog in Figure 13-2 will appear. Select a profile from the drop-down list, and decide whether SQL Mail should start when SQL Server starts. Then click on Test to ensure SQL Mail is working. Click OK, and SQL Mail should be enabled.

Figure 13-1 The SQL Server Enterprise Manager screen with the Support Services folder open.

Figure 13-2 The dialog to set a SQL Mail profile.

Using the extended stored procedures for sending and receiving mail is fairly straightforward if you use most of the defaults. The procedure to send mail is named xp_sendmail .

 xp_sendmail {[@recipients =] '  recipients   [;...  n  ]'}     [,[@message =] '  message  ']     [,[@query =] '  query  ']     [,[@attachments =]   attachments  ]     [,[@copy_recipients =] '  copy  _  recipients   [;...  n  ]'     [,[@blind_copy_recipients =] '  blind_copy_recipients   [;...  n  ]'     [,[@subject =] '  subject  ']     [,[@type =] '  type  ']     [,[@attach_results =] '  attach_value  ']     [,[@no_output =] '  output_value  ']     [,[@no_header =] '  header_value  ']     [,[@width =]   width  ]     [,[@separator =] '  separator  ']     [,[@echo_error =] '  echo_value  ']     [,[@set_user =] '  user  ']     [,[@dbuse =] '  database  '] 

Most of these parameters are not needed. Recipients , subject , and message are often all that you'll need. For example, the Troubleshooter database used in Chapter 9 is used at a site where end users are allowed to enter new troubleshooting documents. The newly entered documents are placed in a pending state so that the subject matter expert can look the article over and verify that the information is correct. Rather than require subject matter area experts to search for pending documents, I had SQL Server use the Insert trigger to send e-mail whenever a record was added. The code to send the e-mail was simple:

 exec xp_sendmail @recipients = '[e-mail]',     @subject = 'There is a new document waiting!',     @message = 'A new document has been added.' 

The other arguments for xp_sendmail are described fully in the MSDN documentation of the SQL Server Books Online.

Reading mail with SQL Mail is similar to reading mail with Simple MAPI, which I covered in Chapter 7. First xp_findnextmsg must be called, and then xp_readmail is called to actually read the mail. The syntax for these two extended stored procedures is listed here. Most of the arguments to the extended stored procedures mirror either Simple MAPI arguments or elements in the MAPI message structures.

 xp_findnextmsg [[@msg_id =] '  message_number  ' [OUTPUT]]     [,[@type =]   type  ]     [,[@unread_only =] '  unread_value  ']) xp_readmail [[@msg_id =] '  message_number  ']     [,[@type =] '  type  ' [OUTPUT]]     [,[@peek =] '  peek  ']     [,[@suppress_attach =] '  suppress_attach  ']     [,[@originator =] '  sender  ' OUTPUT]     [,[@subject =] '  subject  '     OUTPUT]     [,[@message =] '  message  ' OUTPUT]     [,[@recipients =] '  recipients   [;...  n  ]' OUTPUT]     [,[@cc_list =] '  copy_recipients   [;...  n  ]' OUTPUT]     [,[@bcc_list =] '  blind_copy_recipients   [;...  n  ]' OUTPUT]     [,[@date_received =] '  date  '     OUTPUT]     [,[@unread =] '  unread_value  ' OUTPUT]     [,[@attachments =] '  attachments   [;...  n  ]' OUTPUT])     [,[@skip_bytes =]   bytes_to_skip   OUTPUT]     [,[@msg_length =]   length_in_bytes   OUTPUT]     [,[@originator_address =] '  sender_address  '     OUTPUT]] 

Of course, using extended stored procedures to send and receive mail with SQL Server is a primitive operation. All of the limitations of Simple MAPI are present, including the inability to delete messages in folders other than the Inbox. Even with all of these limitations, however, using extended stored procedures this way has some advantages:

• Mail can originate as a by-product of a database operation.
• Users who do not have e-mail access can indirectly send mail.
• There are ways to use e-mail to send query requests and return result sets. MSDN and SQL Server Books Online provide much more information on this feature.

Creating a custom extended stored procedure

The extended stored procedures that come with SQL Server are a good start. Since extended stored procedures are functions exported from a DLL, can we create our own extended stored procedure? Thankfully, the answer is yes.

The Open Data Services API (ODSAPI) provides the tools required to build a DLL that can contain functions exported as extended stored procedures. ODSAPI provides more than 60 functions that support the creation of extended stored procedures. Our example will use just a few of these functions, but all are documented in SQL Server Books Online.

The steps required to create an extended stored procedure using Visual C++ 6 are as follows:

1. Create a new Win32 DLL project.
2. Add an include line for Srv.h to the StdAfx.h file.
3. Add Opends60.lib to the project's libraries.
4. Add the \mssql7\devtools\include directory to the include directories list, and add \mssql7\devtools\lib to the library directories list.
5. A 1-KB copy of Srv.h is included with Microsoft Visual Studio 6. It appears to be missing some crucial information that is included with the 17-KB Srv.h included with SQL Server. Rename the smaller file, or arrange the include directory order so that the larger one is read first. If you are creating a C++ program and you receive unresolved externals on mangled function names while linking, you are probably finding the wrong header.
6. A 39-KB copy of Opends60.lib is included with Visual Studio 6. It appears to be missing some crucial information that is included with the 84-KB Opends60.lib included with SQL Server. Rename the smaller file, or arrange the library directory order so that the larger one is read first. If you are creating a C++ program and you receive error messages about unresolved externals on function names that are exactly as they appear in the header especially srv_paraminfo  while linking, you are probably finding the wrong library.
7. Create a function named __GetXpVersion . Note the two underscores. This function must be exported by your DLL. The function needs to have only a single line of code:

 ULONG APIENTRY __GetXpVersion(void) {     return ODS_VERSION; } 

If trace flag -7260 is set, SQL Server calls this function to determine the version of ODSAPI used. If this callback is not present, or if the version reported is less than what the server requires, a warning message is returned when the function is called.

A great deal of code is required in an extended stored procedure simply to get to the arguments and return a value. From a SQL Server perspective, the function exposed as a stored procedure does not directly accept the number of arguments the extended stored procedure accepts. For instance, if we wanted SQL Server to initiate a call to a beeper, the extended stored procedure would accept two parameters. The first parameter is the beeper number, and the second is the number to be beeped, just like the CallBeeper function in Listing 7-2 in Chapter 7.

Listing 13-3 shows Xp_beep.cpp the code that creates the stored procedure xp_beep .

Listing 13-3

Xp_beep.cpp

 // xp_beep.cpp : xp_beep stored procedure // #include "stdafx.h" #define XP_NOERROR 0 #define XP_ERROR   1 char *ReportTAPIError(LONG ret, char *op, char *buff) {     switch (ret)     {     case LINEERR_INVALAPPNAME:         wsprintf(buff, "\nop=%s Ret=LINEERR_INVALAPPNAME", op);         break;     case LINEERR_OPERATIONFAILED:         wsprintf(buff, "\nop=%s Ret=LINEERR_OPERATIONFAILED", op);         break;     case LINEERR_INIFILECORRUPT:         wsprintf(buff, "\nop=%s Ret=LINEERR_INIFILECORRUPT", op);         break;     case LINEERR_INVALPOINTER:         wsprintf(buff, "\nop=%s Ret=LINEERR_INVALPOINTER", op);         break;     case LINEERR_REINIT:         wsprintf(buff, "\nop=%s Ret=LINEERR_REINIT", op);         break;     case LINEERR_NOMEM:         wsprintf(buff, "\nop=%s Ret=LINEERR_NOMEM", op);         break;     case LINEERR_INVALPARAM:         wsprintf(buff, "\nop=%s Ret=LINEERR_INVALPARAM", op);         break;     case LINEERR_BADDEVICEID:         wsprintf(buff, "\nop=%s Ret=LINEERR_BADDEVICEID", op);         break;     case LINEERR_NODRIVER:         wsprintf(buff, "\nop=%s Ret=LINEERR_NODRIVER", op);         break;     case LINEERR_INCOMPATIBLEAPIVERSION:         wsprintf(buff, "\nop=%s Ret=LINEERR_INCOMPATIBLEAPIVERSION",             op);         break;     case LINEERR_INVALAPPHANDLE:         wsprintf(buff, "\nop=%s Ret=LINEERR_INVALAPPHANDLE", op);         break;     case LINEERR_RESOURCEUNAVAIL:         wsprintf(buff, "\nop=%s Ret=LINEERR_RESOURCEUNAVAIL", op);         break;     case LINEERR_UNINITIALIZED:         wsprintf(buff, "\nop=%s Ret=LINEERR_UNINITIALIZED", op);         break;     case LINEERR_OPERATIONUNAVAIL:         wsprintf(buff, "\nop=%s Ret=LINEERR_OPERATIONUNAVAIL", op);         break;     case LINEERR_NODEVICE:         wsprintf(buff, "\nop=%s Ret=LINEERR_NODEVICE", op);         break;     case LINEERR_ALLOCATED:         wsprintf(buff, "\nop=%s Ret=LINEERR_ALLOCATED", op);         break;     case LINEERR_LINEMAPPERFAILED:         wsprintf(buff, "\nop=%s Ret=LINEERR_LINEMAPPERFAILED", op);         break;     case LINEERR_INCOMPATIBLEEXTVERSION:         wsprintf(buff, "\nop=%s Ret=LINEERR_INCOMPATIBLEEXTVERSION",             op);         break;     case LINEERR_INVALMEDIAMODE:         wsprintf(buff, "\nop=%s Ret=LINEERR_INVALMEDIAMODE", op);         break;     case LINEERR_STRUCTURETOOSMALL:         wsprintf(buff,"\nop=%s Ret=LINEERR_STRUCTURETOOSMALL", op);         break;     case LINEERR_INVALPRIVSELECT:         wsprintf(buff, "\nop=%s Ret=LINEERR_INVALPRIVSELECT", op);         break;     case LINEERR_ADDRESSBLOCKED:         wsprintf(buff, "\nop=%s Ret=LINEERR_ADDRESSBLOCKED", op);         break;     case LINEERR_DIALBILLING:         wsprintf(buff, "\nop=%s Ret=LINEERR_DIALBILLING", op);         break;     case LINEERR_DIALDIALTONE:         wsprintf(buff,"\nop=%s Ret=LINEERR_DIALDIALTONE", op);         break;     case LINEERR_NOTOWNER:         wsprintf(buff, "\nop=%s Ret=LINEERR_NOTOWNER", op);         break;     case LINEERR_DIALPROMPT:         wsprintf(buff, "\nop=%s Ret=LINEERR_DIALPROMPT", op);         break;     case LINEERR_DIALQUIET:         wsprintf(buff, "\nop=%s Ret=LINEERR_DIALQUIET", op);         break;     case LINEERR_INVALCALLHANDLE:         wsprintf(buff, "\nop=%s Ret=LINEERR_INVALCALLHANDLE", op);         break;     case LINEERR_INVALCALLSTATE:         wsprintf(buff, "\nop=%s Ret=LINEERR_INVALCALLSTATE", op);         break;     case LINEERR_INVALCOUNTRYCODE:         wsprintf(buff, "\nop=%s Ret=LINEERR_INVALCOUNTRYCODE", op);         break;     }     return (buff); } int CallBeeper(char *beeperNumber, char *beepTo, char *retBuffer) {     HLINEAPP hLineApp;     HLINE hLine;     char szFriendlyAppName[255];     char szAddress[255];     DWORD dwNumDevs = 0;     DWORD dwAPIVersion = 0x00020000;     DWORD dwCallbackInstance = 43;     DWORD dwStatus;     DWORD dwDeviceID = 0;     LONG ret;     int funcRet = -1;     LINEINITIALIZEEXPARAMS LineInitializeExParams;     LINEEXTENSIONID ExtensionID;     LPLINEDEVCAPS DevCaps = NULL;     strcpy(szFriendlyAppName,         "TAPI Beeper Extended Stored Procedure");     memset((void *)&LineInitializeExParams,         0, sizeof(LINEINITIALIZEEXPARAMS));     LineInitializeExParams.dwTotalSize =         sizeof(LINEINITIALIZEEXPARAMS);     LineInitializeExParams.dwOptions =         LINEINITIALIZEEXOPTION_USEEVENT;     ret = lineInitializeEx(&hLineApp, NULL, NULL, szFriendlyAppName,         &dwNumDevs, &dwAPIVersion, &LineInitializeExParams);     ReportTAPIError(ret, "lineInitializeEx", retBuffer);     if (ret == 0)     {         // It worked!         // Loop through available devices until we find a good one.         for (dwDeviceID = 0; dwDeviceID < dwNumDevs; dwDeviceID++)         {             ret = lineNegotiateAPIVersion(                hLineApp,                 dwDeviceID,                 0x00020000,                 0x00020001,                 &dwAPIVersion,                 &ExtensionID);             if (ret == 0)             {                 ret = lineOpen(hLineApp, dwDeviceID, &hLine,                     dwAPIVersion, 0, dwCallbackInstance,                     LINECALLPRIVILEGE_NONE, 0, 0);                 ReportTAPIError(ret, "lineOpen", retBuffer);                 if (ret == 0)                 {                     break;                 }             }         }         ReportTAPIError(ret, "lineNegotiateAPIVersion", retBuffer);         if (ret == 0)         {             HCALL hCall;             char szLineName[255];             DevCaps = (LPLINEDEVCAPS)new char[1024];             DevCaps->dwTotalSize = 1024;             ret = lineGetDevCaps(hLineApp,                 dwDeviceID, dwAPIVersion, 0, DevCaps);             ReportTAPIError(ret, "lineGetDevCaps", retBuffer);             if (ret >= 0 && DevCaps->dwLineNameSize > 0)             {                 if (DevCaps->dwLineNameSize > (254))                 {                     strncpy (szLineName,                         (LPSTR) DevCaps +                         DevCaps->dwLineNameOffset,                         254);                     szLineName[254] = '\0';                 }                 else                 {                     lstrcpy(szLineName,                         (LPSTR) DevCaps +                         DevCaps->dwLineNameOffset);                 }             }             delete (char *)DevCaps;             // Set the address, which is really a phone number.             wsprintf(szAddress, "%s,,,%s#", beeperNumber, beepTo);             ret = lineMakeCall(hLine, &hCall, szAddress, 0, 0);             if (ret > 0)             {                 wsprintf(retBuffer, "lineMakeCall returned %d", ret);             }             else             {                 funcRet = 0;             }             ReportTAPIError(ret, "lineMakeCall", retBuffer);             while ((dwStatus = WaitForSingleObject(            LineInitializeExParams.Handles.hEvent,                 20000)) == WAIT_OBJECT_0)             {                 LINEMESSAGE lm;                 // TAPI's returning some information...                 if ((ret = lineGetMessage(hLineApp, &lm, 0)) != 0)                 {                 ReportTAPIError(ret, "lineGetMessage", retBuffer);                     return (ret);                 }                 switch (lm.dwMessageID)                 {                 case LINE_REPLY:                     if ((long)lm.dwParam2 < 0)                     {                         wsprintf(retBuffer,                             "\nError occured...Request ID %d",                             lm.dwParam1);                     }                     break;                 case LINE_CALLSTATE:                     if (LINECALLSTATE_DISCONNECTED&lm.dwParam1)                     {                         lineClose(hLine);                     }                     if (LINECALLSTATE_IDLE&lm.dwParam1)                     {                         lineClose(hLine);                     }                 } //...switch (lm.dwMessageID)             }//...while ((dwStatus = WaitForSingleObject(        }//...if (ret == 0)     }     lineShutdown(hLineApp);     return 0; } BOOL APIENTRY DllMain(HANDLE hModule,                        DWORD  ul_reason_for_call,                        LPVOID lpReserved                       ) {     return TRUE; } ULONG APIENTRY __GetXpVersion(void) {     return ODS_VERSION; } // We expect this function to receive 2 parameters. SRVRETCODE xp_beep(SRV_PROC *pSrvProc) {     int paramCount;     int ret;     BYTE cType;     unsigned long cbMaxLen = 255;     unsigned long cbActualLen = 255;     BOOL bNull;     char beeperNumber[255];     char beepTo[255];     char szText[255];     memset(beeperNumber, '\0', 255);     memset(beepTo, '\0', 255);     memset(szText, '\0', 255);     paramCount = srv_rpcparams(pSrvProc);     if (paramCount != 2)     {         return (XP_ERROR);     }     ret = srv_paraminfo(pSrvProc,         1,         &cType,         &cbMaxLen,         &cbActualLen,         (BYTE *)&beeperNumber[0],         &bNull);     if (cType != SRVBIGCHAR &&         cType != SRVBIGVARCHAR &&         cType != SRVVARCHAR)     {         return (XP_ERROR);     }     if (cbActualLen > 255)     {         return (XP_ERROR);     }     cbMaxLen = cbActualLen = 255;     ret = srv_paraminfo(pSrvProc,         2,         &cType,         &cbMaxLen,         &cbActualLen,         (BYTE *)&beepTo[0],         &bNull);     if (cType != SRVBIGCHAR &&         cType != SRVBIGVARCHAR &&         cType != SRVVARCHAR)     {         return (XP_ERROR);     }     if (cbActualLen > 255)     {         return (XP_ERROR);     }     __try     {         __try         {             if (CallBeeper(beeperNumber, beepTo, szText) == 0)             {                 wsprintf(szText, "Calling %s with %s",                     beeperNumber, beepTo);             }         }         __except(EXCEPTION_EXECUTE_HANDLER)         {             strcpy(szText, "Exception in XP_BEEP");         }     }     __finally     {         ret = srv_describe(pSrvProc, 1,             "Message", SRV_NULLTERM,             SRVBIGVARCHAR, strlen(szText),             SRVBIGVARCHAR, strlen(szText),             szText);         ret = srv_sendrow(pSrvProc);         ret = srv_senddone(pSrvProc,             (SRV_DONE_COUNT  SRV_DONE_MORE),             0, 1);     }     return (XP_NOERROR); } 

At the top of the listing two constants are defined that will signal success or failure as the return code. Next come several functions that are similar to the functions in Listing 7-2. There are a couple of changes. First ReportTAPIError accepts three parameters, the third being a buffer that will accept the translated error. Next CallBuffer accepts an additional parameter the return buffer that will receive a string reporting on the success or failure of the operation.

The __GetXpVersion function is as described in "Creating a custom extended stored procedure". The heart of the stored procedure is xp_beep , the last procedure defined in Listing 13-3. The prototype for this procedure is common to all extended stored procedures.

 SRVRETCODE xp_beep(SRV_PROC *pSrvProc) 

The pSrvProc parameter is used as a handle for other functions related to creating an extended stored procedure. From this handle, the stored procedure code can discover the type and number of parameters passed to the extended stored procedure. The parameter count is returned by a call to srv_rpcparams , a function that accepts as a single parameter pSrvProc , the parameter to the stored procedure. The parameter count is tested against the number required. If too few were sent, an error is returned.

Once the parameter count is retrieved, the next step is to retrieve the parameters and validate that the parameter types are correct. For each expected parameter, srv_paraminfo is called.

 int srv_paraminfo (    SRV_PROC * srvproc,     int n,     BYTE * pbType,     ULONG * pcbMaxLen,     ULONG * pcbActualLen,     BYTE * pbData,     BOOL * pfNull); 

The first parameter is the pointer passed to the extended stored procedure. The second parameter, n , is the parameter number. Parameters are counted starting with 1, so the first parameter is 1, the second 2, and so on. The type of parameter is returned in the variable pointed to by pbType . The maximum and actual lengths are returned in pcbMaxLen and pcbActualLen , respectively. The actual parameter data is passed back in the buffer pointed to by bpData . If the parameter is NULL, the variable pointed to by pfNull is set to TRUE.

For each of the two expected parameters we perform two checks, one to verify the correct type and one to verify the maximum length. The code to do this is as follows:

 if (cType != SRVBIGCHAR &&     cType != SRVBIGVARCHAR &&     cType != SRVVARCHAR) {     return (XP_ERROR); } if (cbActualLen > 255) {     return (XP_ERROR); } 

The possible data types are covered in MSDN in the Open Data Services Reference. In this example, I am filtering to ensure a normal character type is passed. Once the parameters have been validated, CallBeeper is called inside a __try / __except block. This is to ensure that any exception thrown by CallBeeper does not bubble up beyond the extended stored procedure DLL.

To return a result to SQL Server, we create a single column to be part of the result set. This requires three steps. First we call srv_describe :

 int srv_describe (    SRV_PROC * srvproc,     int colnumber,     DBCHAR * column_name,     int namelen,     DBINT desttype,     DBINT destlen,     DBINT srctype,     DBINT srclen,     void * srcdata); 

The first parameter is the pointer passed as the parameter to the extended stored procedure. The second parameter is colnumber , the column number of this parameter in the result set. The name of the column is sent as column_name . The balance of the parameters are self-explanatory. The desttype and srctype parameters contain Open Data Services data types, the same type passed in to srv_paraminfo as pbType . In the second step, after the row is described and set, we call srv_sendrow with a single parameter, pSrvProc  the parameter passed to the extended stored procedure. Third we call srv_senddone .

 int srv_senddone (    SRV_PROC * srvproc,     DBUSMALLINT status,     DBUSMALLINT info,     DBINT count); 

The first parameter is the same pointer passed to all Open Data Services functions. The status parameter can contain one or more of the flags described in Table 13-1.

Table 13-1 Flag Values for Status Parameter of srv_senddone

Status Flag	Meaning
SRV_DONE_FINAL	The current result set is the final result.
SRV_DONE_MORE	The current result set is not the final result.
SRV_DONE_COUNT	The count parameter contains a valid count.
SRV_DONE_ERROR	The current client command receives an error.
SRV_DONE_RPC_IN_BATCH	The current results are the final results in this batch of remote stored procedures.

In the example, status is set to SRV_DONE_FINAL SRV_DONE_COUNT, meaning that thisis the last result and the count parameter has a valid record count 1. The third parameter is unused, and 0 should be passed.

Installing and using our extended stored procedure

Once we create our extended stored procedure, we should copy it to a directory where SQL Server can find it. When we've done that, the easiest way to interactively test the extended stored procedure is to load the SQL Server Query Analyzer. A stored procedure, sp_addextendedproc , is used to install the extended stored procedure. This stored procedure expects two parameters, the name of the extended stored procedure function (which is case-sensitive) and the name of the DLL (which has the DLL extension). Optionally, the name of the extended stored procedure can be prefixed by the owner name in the form owner.function . Figure 13-3 shows the screen after xp_beep has been installed.

Figure 13-3 SQL Server Query Analyzer showing xp_beep being installed.

Figure 13-4 shows how the extended stored procedure is called and the results returned. To remove the extended stored procedure, sp_dropextendedproc can be called with the name of the extended stored procedure as its single parameter.

Figure 13-4 Running xp_beep in the SQL Server Query Analyzer.