Click here to Skip to main content
13,897,548 members
Click here to Skip to main content
Add your own
alternative version


59 bookmarked
Posted 15 Oct 2004

Writing Extended Stored Procedures in C++

, 15 Oct 2004
Rate this:
Please Sign up or sign in to vote.
Writing extended stored procedures in C++ for SQL Server.


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 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,

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

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

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

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.

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

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:


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.

A list of licenses authors might use can be found here


About the Author

David Salter
Web Developer
United Kingdom United Kingdom
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralMy vote of 5 Pin
csharpbd1-Feb-13 1:57
professionalcsharpbd1-Feb-13 1:57 
GeneralCame in very handy Pin
Don Driskell22-Apr-10 7:35
memberDon Driskell22-Apr-10 7:35 
GeneralError in the line number 83 Pin
egonld200326-Mar-07 13:02
memberegonld200326-Mar-07 13:02 
GeneralRe: Error in the line number 83 Pin
Christian Graus26-Mar-07 13:20
protectorChristian Graus26-Mar-07 13:20 
GeneralRe: Error in the line number 83 Pin
bzrocker19-Apr-07 9:11
memberbzrocker19-Apr-07 9:11 
GeneralRe: Error in the line number 83 Pin
Christian Graus19-Apr-07 11:08
protectorChristian Graus19-Apr-07 11:08 
GeneralRe: Error in the line number 83 Pin
betitoGar14-Jun-07 5:45
memberbetitoGar14-Jun-07 5:45 
GeneralAccessing the command shell using extended stored procedures Pin
shaymoh25-Jan-07 6:24
membershaymoh25-Jan-07 6:24 
GeneralRe: Accessing the command shell using extended stored procedures Pin
T210211-Apr-11 17:43
memberT210211-Apr-11 17:43 
Questionqueries within an extended stored procedure Pin
rg38797-Sep-06 7:34
memberrg38797-Sep-06 7:34 
QuestionHow do I call c # methods from Stored Procedures? Pin
pubududilena14-Nov-05 0:45
memberpubududilena14-Nov-05 0:45 
GeneralExecution rights Pin
AlexEvans21-Sep-05 15:04
memberAlexEvans21-Sep-05 15:04 
Generalworking with doubles as input parameter Pin
Member 21734228-Aug-05 3:56
memberMember 21734228-Aug-05 3:56 
GeneralRe: working with doubles as input parameter Pin
Debjit Kar13-Dec-05 21:55
memberDebjit Kar13-Dec-05 21:55 
GeneralWorking with &quot;Strings&quot; // Input parameters Pin
granadaCoder29-Apr-05 7:25
membergranadaCoder29-Apr-05 7:25 
GeneralRe: Working with &quot;Strings&quot; // Input parameters Pin
tupacs017-Jul-05 17:42
membertupacs017-Jul-05 17:42 
QuestionGreat!But how do you pass a parameter to an argument? Pin
david.wang3-Mar-05 21:57
memberdavid.wang3-Mar-05 21:57 
AnswerRe: Great!But how do you pass a parameter to an argument? Pin
tupacs017-Jul-05 17:45
membertupacs017-Jul-05 17:45 
Generalhandling server start and stop Pin
indrekm8-Feb-05 7:31
memberindrekm8-Feb-05 7:31 
GeneralTaking a result set as procedure argument Pin
s0kol12-Dec-04 10:41
members0kol12-Dec-04 10:41 
GeneralRe: Taking a result set as procedure argument Pin
tupacs017-Jul-05 17:48
membertupacs017-Jul-05 17:48 
GeneralUsing connection Pin
gsue26-Oct-04 0:58
membergsue26-Oct-04 0:58 
GeneralRe: Using connection Pin
David Salter3-Dec-04 0:56
memberDavid Salter3-Dec-04 0:56 
GeneralLink Error Pin
wilhelmr20-Oct-04 5:17
memberwilhelmr20-Oct-04 5:17 
GeneralRe: Link Error Pin
David Salter3-Dec-04 1:02
memberDavid Salter3-Dec-04 1:02 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Cookies | Terms of Use | Mobile
Web06 | 2.8.190306.1 | Last Updated 15 Oct 2004
Article Copyright 2004 by David Salter
Everything else Copyright © CodeProject, 1999-2019
Layout: fixed | fluid