Extended SQL2K StoredProc

An extended stored procedure is just a C/C++ DLL that SQL Server loads in-process and calls through the SRV_PROC API - no CLR required. This one is the minimal complete example: it validates that exactly one parameter was passed, describes a two-column result set (an int counter and a varchar greeting), reads the parameter, and loops back numRows rows via srv_sendrow. Use it as a template whenever you need to drop native code into SQL Server 2000/2005 and return a result set from C++. Note the double call to srv_paraminfo - first with a NULL data pointer to learn the size, second with your buffer to actually retrieve the value.

//////////////////////////////////////////////////////////////////////////////////////////////////////////////

#include "stdafx.h"
#include "xp_example.h"

//////////////////////////////////////////////////////////////////////////////////////////////////////////////

#define MAXCOLNAME				50
#define MAXNAME					25
#define MAXTEXT					255

//////////////////////////////////////////////////////////////////////////////////////////////////////////////

BOOL APIENTRY DllMain(HANDLE hModule, DWORD dwReason, LPVOID lpReserved)
{
    return TRUE;
}

//////////////////////////////////////////////////////////////////////////////////////////////////////////////

RETCODE __declspec(dllexport) xp_example(SRV_PROC *srvproc)
{
	DBCHAR spText[MAXTEXT];
    DBCHAR colname[MAXCOLNAME];

	// Check that there are the correct number of parameters.
	if ( srv_rpcparams(srvproc) != 1 )
	{
		// If there is not exactly one parameter, send an error to the client.
		_snprintf(spText, MAXTEXT, "ERROR. You need to pass one parameter.");
		srv_sendmsg( srvproc, SRV_MSG_INFO,	0,(DBTINYINT)0,	(DBTINYINT)0,NULL,0,0,spText,SRV_NULLTERM);

		// Signal the client that we are finished.
		srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)0);

		return XP_ERROR;
	}

	// Define column 1
    _snprintf(colname, MAXCOLNAME, "ID");
    srv_describe(srvproc, 1, colname, SRV_NULLTERM, SRVINT4, sizeof(DBSMALLINT), SRVINT2, sizeof(DBSMALLINT), 0);

	// Define column 2
    _snprintf(colname, MAXCOLNAME, "Hello World");
    srv_describe(srvproc, 2, colname, SRV_NULLTERM, SRVCHAR, MAXTEXT, SRVCHAR, 0, NULL);

	BOOL bNull;
	BYTE bType;
	ULONG uLen;
	ULONG uMaxLen;

	// Get the info about the parameter.  
	// Note pass NULL for the pbData parameter to get information rather than the parameter itself.
	srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, NULL, &bNull); 


	// Create some memory to get the parameter in to.
	BYTE* Data = new BYTE[uLen];
	memset(Data, '\0', uLen);

	// Get the parameter
	srv_paraminfo(srvproc, 1, &bType, &uMaxLen, &uLen, Data, &bNull); 

	// Convert the parameter into a long from the byte*
	long numRows = (long)*Data;

	long i = 1;

	// Generate "numRows" output rows.
	for(i = 1; i <= numRows; i++ )
	{
		// Set the first column to be the count.
		srv_setcoldata(srvproc, 1, &i);

		// Set the second column to be a text string
		int ColLength = _snprintf(spText, MAXTEXT, "Hello from the extended stored procedure. %d", i);
		srv_setcoldata(srvproc, 2, spText);
	    srv_setcollen(srvproc, 2, ColLength);

		// Send the row back to the client
	    srv_sendrow(srvproc);
	}
  
	// Tell the client we're done and return the number of rows returned.
	srv_senddone(srvproc, SRV_DONE_MORE | SRV_DONE_COUNT, (DBUSMALLINT)0, (DBINT)i);

	// Tidy up.
	delete []Data;

	return XP_NOERROR ;
}

//////////////////////////////////////////////////////////////////////////////////////////////////////////////

After building the DLL, register it with sp_addextendedproc 'master..xp_example', 'path\to\xp_example.dll' and call it like any other stored proc: EXEC xp_example 10 returns ten numbered rows of greetings.