Extended stored procedure component for SQL Server 7.0/2000/2005

1 What are Extended stored procedures?

Extended stored procedure is a special function packaged in a DLL.

Extended stored procedures for SQL Server can be written either in Delphi or C/C++ (or any other compiler which supports function export — VB and C# can't unless of use SQL Server 2005). And yes, all the WinAPI can be used there.

Like regular T-SQL stored procedures (or .NET extended stored procedures for SQL Server 2005), they can accept input/output parameters and produce recordsets.

The great difference is that they can accept a variable list of input parameters of different datatypes! So, actual number of parameters and their datatypes can be determined by extended stored procedure at runtime.

2 What are they good for?

Extended stored procedures are good for:

• Tasks that are complex or impossible to implement in Transact SQL (for example, file system access, or use of complex external APIs such as cryptographics or internet access)
• Stored procedures that must be called from user defined functions (UDF)
• Business logic to be hidden from others.

3 Is that easy to write extended stored procedures?

Although giving these benefits, the native Extended Stored Procedures API (former Open Data Services API) is rather complex in use.

It requires too much «housekeeping» work from the developer: manage memory buffers, handle I/O parameters, determine I/O datatypes, describe and produce recordsets, etc, etc, etc… This, in turn, may require often debug when implementing different projects.

Is there a better way?

4 The better way: a Delphi component!

Here is a Delphi class TXProc that hides all of that jazz from the developer and just exposes familiar Delphi methods and properties. It works in Delphi 5, 6, 7, 2005, and 2006.

No more dozens of srv_describe, srv_paramsetoutput, srv_rpcparams, srv_paraminfo, etc!

Just with myXProc do Fields[0].AsInteger := Params.ByName('@IntVal').AsInteger.

This is a real world component: It works as a part of a complex accounting system, 7 days a week, 24 hours a day.

5 Delphi Extended stored procedure Example

[+] Example (click here to show/hide):

• Creates an TXProc object,
• Reads the number of input parameters,
• Defines a recordset column, and
• Handles possible ecxeptions

library xpMyProc;

uses
  SysUtils, XProc;

function xp_MyProc(Handle: TXProcHandle): Integer; cdecl;
begin
  result := 0;		// Assume failure

  with TXProc.Create(Handle) do
  try
    try
      // YOUR CODE BEGINS HERE…

      // Check I/O parameters…
      if Params.Count > 0 then
        // There are some parameters
      else
        // No parameters were specified
      ;

      // Define recordset columns…
      Fields.Define(0, ftIntefer, 'Int Column Name');
      // Return recordset here…

      // …YOUR CODE ENDS HERE
      result := 1;		// Succeed
    except
      on E: Exception do
      begin
       // Print error to SQL Query Analyzer
       RaiseError('xp_MyProc: ' + E.Message);
       // Exception flag or custom error code
       result := -1;
      end;
    end;
  finally
    Free;
  end;
end; exports xp_MyProc;

6 Component features

The component supports the following datatypes for I/O Parameters and Recordset Fields:

bigint (recoginzed as numeric) binary bit datetime uniqueidentifier (TGUID in Delphi) float

int money numeric or decimal nvarchar varbinary varchar

Plus, it supports the following BLOB datatypes for Recordset Fields:

• image
• ntext
• text

Maximum length of varchar/nvarchar parameters data is 4000 symbols.

An arbitrary number of recordsets can be returned from a procedure.

Note: Instead of SQL Server 2000 (and later), SQL Server 7.0 does not support SELECT INTO/INSERT of text/ntext/image fields from extended stored procedure recordsets.

Download Demo (120KB) Includes: Demo DLL and its source project Demo DCU for Delphi 5,6,7 Complete reference manual in *.CHM format Note DCUs have limited demo functionality: Only parameters and fields of SQL datatype int can be accessed (see TxpItem.AsInteger property in the Manual) Output parameters are not supported Recordsets are supported

7 Buy component source code

Instant Delivery! Receive this product immediately* right after you have made your order! * For payments made by credit or debit cards, or PayPal.

Component source code (full version) for Delphi 5, 6, 7, 2005, and 2006 Show volume discount prices. BUY NOW!

Show our Privacy Policy and Order Processing information.