Contact   About
Apps for Windows Source Code SQL Server Extensions  

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 srv_describe, srv_paramsetoutput, srv_rpcparams, srv_paraminfo, etc!
Just CXProc xproc(srvproc); CxpFields& fd = xproc.Fields(); fd[0].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 image.

6. Class features 

This class supports the following datatypes for I/O Parameters and Recordset Fields:
  • bigint (recoginzed as numeric)
  • binary
  • bit
  • datetime
  • uniqueidentifier
  • 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
    • Header source file: CXProc.h
  2. Read our Privacy Policy and Order Processing information.
  3. Buy the Extended stored procedure VC++ class source code using a safe payment processing service from Share*it!. See Volume discount prices.

Related Documents

File 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
Copyright © 2003-2017
Share |