Extended stored procedure class 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 C/C++ or Delphi (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 VC++ class 

Here is a VC++ class CXProc that hides all of that jazz from the developer and just exposes just methods and properties.
It works in VC++ 6 and 7.

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.

#include «xproc.h»

using namespace XProc;

__declspec(dllexport) RETCODE xp_Class(SRV_PROC *srvproc)
{
	CXProc xproc(srvproc);				// Initialize
	CxpParams& p = xproc.Params();			// Remember the INPUT/OUTPUT params collection for future reference

	size_t cnt = 0;

	if (p.size()) {					// Check if procedure parameters are present
		char* s = p[0].GetAnsiText();		// Read first procedure parameter (assume it is VARCHAR & OUTPUT)
		
		cnt = 21 + strlen(s);
		char* answer = new char[cnt];
		memset(answer, 0, cnt);
		strcat(answer, «You've just passed: „);

		p[0].SetVarchar(strcat(answer, s));		// Send the modified text back to OUTPUT parameter
		delete answer; delete s;			// IMPORTANT!!!

	}

 	// Let's make a recordset of 20 rows and 3 columns: INT, DATETIME, and IMAGE
	CxpFields& fd = xproc.Fields();			// Remember the fields collection for future reference
    
	cnt = 300;					// Let IMAGE field's length is 300 bytes
	fd[2].SetName(«300 bytes of BLOB»);			// Give an arbitrary name to an IMAGE field column

 	// Just a dummy integer multiplicator value from 2nd INPUT parameter (optional)
	int nDummy = 1;	
	if ((p.size() > 1) && (p[1].DataType() == ftInteger)) {
		nDummy = p[1].GetInt();
	}

	SYSTEMTIME st;
	for (int i = 0; i < 20; i++) {
		// Field #1
		fd[0].SetInt(i * nDummy);
		// Field #2
		GetSystemTime(&st); fd[1].SetDateTime(st);		
		// Field #3
		void* p = malloc(cnt); memset(p, i, cnt); fd[2].SetImage(p, (ULONG)cnt); free(p);
		// Send a recordset row back to SQL Server
		fd.Next();
	}

	return 1;
};

6 Class features 

This class 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 Header source file: CXProc.h Complete reference manual in *.CHM format

7 Buy 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.

Class source code (full version) for VC++ 6 and 7 Show volume discount prices. BUY NOW!

Show our Privacy Policy and Order Processing information.