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 is Extended Stored Procedure 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. Is that simple writing 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, such as:
- manage memory buffers,
- handle I/O parameters,
- determine I/O datatypes,
- describe and produce recordsets,
- etc, etc, etc…
This, in turn, may require often debugging when implementing different projects.
Is there a better way?
4. Delphi Component: The Better Way!
TXProc Delphi class hides all the low-level details from the programmer, and exposes familiar Delphi methods and properties simplifying the development process. It works well in Delphi 5, 6, 7, 2005, 2006, and 2007.
No more dozens of
with myXProc do Fields.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
6. Component features
The component supports the following datatypes for I/O Parameters and Recordset Fields:
bigint (recoginzed as
TGUID in Delphi)
Plus, it supports the following BLOB datatypes for Recordset Fields:
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
fields from extended stored procedure recordsets.
7. Get It From Here!
- 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.
- 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.
This article is also available in Croatian.
Related DocumentsFile Access Stored Procedures for SQL Server 2005 and later (.NET)File Access Stored Procedures for SQL Server 7.0, 2000, 2005 and later (Win32)C++ Extended stored procedure class for SQL Server 7.0, 2000, 2005