C++ Extended stored procedure class 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 C/C++ or
Delphi (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 and later), 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. VC++ Class: The Better Way!
CXProc VC++ class hides all the low-level details from the programmer, and exposes just convenient methods and properties simplifying the development process. It works well in VC++ 6, 7 and later.
No more dozens of
CXProc xproc(srvproc); CxpFields& fd = xproc.Fields(); fd.SetInt(123);
5. VC++ Extended Stored Procedure Example
[+] Example (click here to show/hide):
This code deals with two optional I/O parameters
(first them is
varchar and second is
int), modifies first (when present),
and then creates a recordset of 3 columns:
int, datetime, and
6. Class features
This class supports the following datatypes for I/O Parameters and Recordset Fields:
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
- Header source file: CXProc.h
- Buy the Extended stored procedure VC++ class source code using a safe payment processing service from Share*it!. See Volume discount prices.
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)Delphi extended stored procedure component for SQL Server 7.0, 2000, 2005