Writing Extended Stored Procedures in C++

• Download source files - 4.11 Kb

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: