Delphi extended stored procedure component for SQL Server 7.0/2000/2005

1. What is Extended stored procedure?

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++ (Since SQL Server 2005 it is possible to create extended stored procedures in C#). Like regular T-SQL stored procedures (or .NET extended stored procedures for SQL Server 2005), they can accept input/output parameters and produce recordsets. And yes, all the WinAPI can be used there.

The significant difference between a regular T-SQL stored procedure and an extended stored procedure is that the latter allows variable list of input parameters of different datatypes!
So, the actual number of parameters and their datatypes can be determined by an 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 using 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.

3. Writing extended stored procedures — is that simple?

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, such as:

• manage memory buffers,
• handle I/O parameters,
• determine I/O datatypes,
• describe and produce recordsets,
• etc, etc, etc…

Is there a better way?

4. Delphi Component: The Better Way!

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.

7. Get It From Here!

1. Try the demo of 120K download size including:
   • Complete user manual in *.CHM format
   • Demo DLL and its source code
   • Demo DCU for Delphi 5,6,7. Note that these DCUs have limited demo functionality: Only parameters and fields of SQL datatype int can be accessed (see TxpItem.AsInteger property in the Manual), and Output parameters are not supported.
2. Read our Privacy Policy and Order Processing information.
3. Buy the Extended stored procedure component source code for Delphi 5, 6, 7, 2005, 2006 using a safe payment processing service from Share*it!. See Volume discount prices.

Related Documents