Introduction

This article demonstrates how to write SQL Server extended stored procedures using C++. Visual Studio 2003 Enterprise Edition has got a Wizard for creating extended stored procedures, but in this article, I show how easy they are to create without using the wizard.

Background

Extended Stored Procedures are DLLs that run within the address space of SQL server. They are accessed like any other stored procedure in SQL Server except that they have to be registered with SQL Server first.

Registering an extended stored procedure can be done using the sp_addextendedproc stored procedure. This takes the name of the extended procedure to register and the name of the DLL that hosts it.

exec sp_addextendedproc 'xp_example', 'xp_example.dll'

To remove an extended stored procedure, use the sp_dropextendedproc procedure passing it the name of the procedure to drop.

exec sp_dropextendedproc 'xp_example'

Creating an Extended Stored Procedure

To create an extended stored procedure in Visual Studio, you need to first create a standard DLL project. Using the New Project wizard, create a Win32 app C++ project and specify the output as a DLL. To allow the DLL to build correctly, you need to link with the opends60.lib library.

That's all there is to creating the DLL to host an extended stored procedure! You can see that the Wizard doesn't really do much.

All extended stored procedures have the same C signature so that SQL Server can execute them correctly. This signature is shown below and is the entry point by which SQL Server calls the procedure.

RETCODE __declspec(dllexport) xp_example(SRV_PROC *srvproc)

Passing Parameters to the procedure

One of the first things to do when writing an extended procedure is to check that the number of parameters passed from the client is correct. The method of doing this is shown below:

// 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;
}

To check the number of parameters, we call the srv_rpcparams method. In this example, we are expecting one parameter, so if any other number of parameters are passed, we need to flag an error and stop execution of the procedure.

If the number of parameters is incorrect, the srv_sendmsg method is used to send a text string back to the client. In this simple example, a string is returned with a simple message.

After sending the message, we need to indicate to the client that the stored procedure has finished executing. This is done using the srv_senddone method. You can see that the second parameter of this function is SRV_DONE_ERROR which indicates that an error is being returned to the client. Finally, we return XP_ERROR from the method.

Now that we know we have the correct number of parameters, we need to get the information about them. This is done using the srv_paraminfo method.

// 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);

Two calls are made to this method. The first passes NULL as the pbData parameter. This causes the size of the buffer required to hold the parameter to be returned without returning the parameter itself. After the first invocation, a buffer is created and then the method called again. This time the parameter is returned into the variable Data.

Creating columns in the resultset

To define columns for the SQL resultset, the srv_describe method is used. This method specifies the type of the column (SRVINT4 and SRVCHAR in this example) together with the length of the column (MAXTEXT for the char column).

Returning results to the client

Finally, it's time to return rows to the client. This is done using the srv_setcoldata, srv_setcollen and srv_sendrow methods as shown below.

Each column is initialized using the srv_setcoldata. Finally, when all the columns have been defined, the row is sent to the client using the srv_sendrow method.

// Generate "numRows" output rows.
for ( long 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);
}

Ending the procedure

To notify SQL Server that the processing has finished and to return results to the client, we call the srv_senddone method, although this time, we pass the parameter SRV_DONE_MORE|SRV_DONE_COUNT indicating that there has not been an error and that execution is complete. The last parameter specifies the number of rows returned to the client.

Running the example

To run the example, compile the example code to create a DLL called xp_example.dll and then copy this DLL into the MSSQL/Binn directory for SQL Server. Log on to SQL Server as sa and register the stored procedure by executing exec sp_addextendedproc 'xp_example', 'xp_example.dll'.

You can then test the code by executing exec xp_example 5. This will generate results as shown below:

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

You have to call srv_paramstatus() to make sure it's an output parameter first. Then you call srv_paramsetoutput() to set the value of the OUTPUT parameter. I'm working on a class that encapsulates all of this so you can make simple method calls on an object (see post above for a sample). I'd love to get some feedback. Thanks.

Is taking a result set as procedure argument possible?I'm trying to implement a temporal layer on SQL Server 2000. I need to take a result set returned by a SELECT statement, analyze and change it. It seems impossible to accomplish in Transact-SQL because of the limitations (my proc is supposed to be universal, meaning to work for tables not defined at development time). I need to figure out a way by thursday so please let me know if you've got any idea how to do it on server-side. Thanks a lot in advance.

Hi, the article is fine but I would like to conect to the database from within the procedure and return some records/perform operations. I do not want to open completely "separate" connection but rather bind it with current user session and operate depending on the user rights.Sorry if it is unclear

From BOL: "Bound connections allow two or more connections to share the same transaction and locks. Bound connections can work on the same data without lock conflicts. Bound connections can be created from multiple connections within the same application, or from multiple applications with separate connections. Bound connections make coordinating actions across multiple connections easier."

I tried to compile the sample with VC++ 6.0. I have to replace the srv_paraminfo call as this does not exist. During link I get all srv_ calls as unresolved externals. I have specified the opends60.lib.

You can certainly write your extended procedures in C++. Just remember that in order for SQL server to see them, the functions exported from the DLL have to use "C" calling convention. I accomplish this in the function definition:

Hi.. this is indeed a great article. If I wish to execute the Extended SP from another database, how do I get this done? I tried prefixing the sp as master.spname as per the documentation in MSDN but this doesnt work either

That's because Data is defined as a BYTE (unsigned char) pointer. The easiest way I've found around this is to define a union that includes an int pointer for integer data, a BYTE pointer for CHAR/VARCHAR data, and pointers to other types I might want to retrieve in my input parameters.