Click here to Skip to main content
Click here to Skip to main content

Writing Extended Stored Procedures in C++

By , 15 Oct 2004
Rate this:
Please Sign up or sign in to vote.

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

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

    // 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:

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.

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

Comments and Discussions

 
GeneralMy vote of 5 Pinmembercsharpbd1-Feb-13 1:57 
GeneralCame in very handy PinmemberDonDriskell22-Apr-10 7:35 
Thank you. This article really helped get me started on CPP Extended STP's.
GeneralError in the line number 83 Pinmemberegonld200326-Mar-07 13:02 
GeneralRe: Error in the line number 83 PinstaffChristian Graus26-Mar-07 13:20 
GeneralRe: Error in the line number 83 Pinmemberbzrocker19-Apr-07 9:11 
GeneralRe: Error in the line number 83 PinstaffChristian Graus19-Apr-07 11:08 
GeneralRe: Error in the line number 83 PinmemberbetitoGar14-Jun-07 5:45 
GeneralAccessing the command shell using extended stored procedures Pinmembershaymoh25-Jan-07 6:24 
GeneralRe: Accessing the command shell using extended stored procedures PinmemberT210211-Apr-11 17:43 
Questionqueries within an extended stored procedure Pinmemberrg38797-Sep-06 7:34 
QuestionHow do I call c # methods from Stored Procedures? Pinmemberpubududilena14-Nov-05 0:45 
GeneralExecution rights PinmemberAlexEvans21-Sep-05 15:04 
Generalworking with doubles as input parameter PinmemberStephan Aiche8-Aug-05 3:56 
GeneralRe: working with doubles as input parameter PinmemberDebjit Kar13-Dec-05 21:55 
GeneralWorking with &quot;Strings&quot; // Input parameters Pinmembersholliday29-Apr-05 7:25 
GeneralRe: Working with &quot;Strings&quot; // Input parameters Pinmembertupacs017-Jul-05 17:42 
QuestionGreat!But how do you pass a parameter to an argument? Pinmemberdavid.wang3-Mar-05 21:57 
AnswerRe: Great!But how do you pass a parameter to an argument? Pinmembertupacs017-Jul-05 17:45 
Generalhandling server start and stop Pinmemberindrekm8-Feb-05 7:31 
GeneralTaking a result set as procedure argument Pinmembers0kol12-Dec-04 10:41 
GeneralRe: Taking a result set as procedure argument Pinmembertupacs017-Jul-05 17:48 
GeneralUsing connection Pinmembergsue26-Oct-04 0:58 
GeneralRe: Using connection PinmemberDavey3-Dec-04 0:56 
GeneralLink Error Pinmemberwilhelmr20-Oct-04 5:17 
GeneralRe: Link Error PinmemberDavey3-Dec-04 1:02 
QuestionCould scalar function be written in C++? PinmemberZhuofei Wang20-Oct-04 0:26 
AnswerRe: Could scalar function be written in C++? PinmemberPaul A. Howes22-Oct-04 5:18 
GeneralExecuting an Extended SP from another DB PinmemberSriram Srivatsan17-Oct-04 19:49 
GeneralRe: Executing an Extended SP from another DB PinmemberDavey18-Oct-04 0:07 
GeneralGreat Article! PinmemberXiangyang Liu16-Oct-04 19:56 
GeneralRe: Great Article! PinmemberDavey17-Oct-04 9:03 
GeneralRe: Great Article! PinmemberXiangyang Liu18-Oct-04 19:14 
GeneralRe: Great Article! Pinmembertupacs017-Jul-05 17:54 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140415.2 | Last Updated 15 Oct 2004
Article Copyright 2004 by David Salter
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid