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.
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
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:
if ( srv_rpcparams(srvproc) != 1 )
_snprintf(spText, MAXTEXT, "ERROR. You need to pass one parameter.");
srv_sendmsg( srvproc, SRV_MSG_INFO, 0,(DBTINYINT)0,
srv_senddone(srvproc, SRV_DONE_ERROR, (DBUSMALLINT)0, (DBINT)0);
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(srvproc, 1, &bType, &uMaxLen, &uLen, NULL, &bNull);
BYTE* Data = new BYTE[uLen];
memset(Data, '\0', uLen);
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
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 (
SRVCHAR in this example) together with the length of the column (
MAXTEXT for the
_snprintf(colname, MAXCOLNAME, "ID");
srv_describe(srvproc, 1, colname, SRV_NULLTERM, SRVINT4,
sizeof(DBSMALLINT), SRVINT2, sizeof(DBSMALLINT), 0);
_snprintf(colname, MAXCOLNAME, "Hello World");
srv_describe(srvproc, 2, colname, SRV_NULLTERM,
SRVCHAR, MAXTEXT, SRVCHAR, 0, NULL);
Returning results to the client
Finally, it's time to return rows to the client. This is done using the
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
for ( long i = 1; i <= numRows; i++ )
srv_setcoldata(srvproc, 1, &i);
int ColLength = _snprintf(spText, MAXTEXT,
"Hello from the extended stored procedure. %d", i);
srv_setcoldata(srvproc, 2, spText);
srv_setcollen(srvproc, 2, ColLength);
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.
srv_senddone(srvproc, SRV_DONE_MORE | SRV_DONE_COUNT, (DBUSMALLINT)0, (DBINT)i);
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: