Extended Stored Procedure Sample: xp_hello

Extended Stored Procedure Programming

Extended Stored Procedure Programming

Extended Stored Procedure Sample: xp_hello

These code portions from xp_hello illustrate the basics of writing an extended stored procedure. The complete code for this example is available in the Samples\ODS directory. This is in a sample only available if you select Dev Tools in setup.

Transact-SQL Script
-- TSQL script exercising xp_hello sample
use master
go

sp_addextendedproc 'xp_hello', 'xp_hello.dll'
go

-- Call xp_hello with literal parameters
declare @txt varchar(33)
exec xp_hello @txt OUTPUT
select @txt AS OUTPUT_Parameter
go

sp_dropextendedproc 'xp_hello'
go

dbcc xp_hello(free)
go

-- EXPECTED RESULTS
-- Column 1:
-- ---------
-- Hello World!

-- (1 row(s) affected)

-- OUTPUT_PARAMETER
-- ----------------
-- Hello World!

-- (1 row(s) affected)
C Source Code
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
#include <ctype.h>
#include <windows.h>
#include <srv.h>
#include <time.h>

// Macros -- return codes
#define XP_NOERROR      0
#define XP_ERROR        1

#define MAX_SERVER_ERROR 20000
#define XP_HELLO_ERROR MAX_SERVER_ERROR+1

void printUsage (SRV_PROC* pSrvProc);
void printError (SRV_PROC *pSrvProc, CHAR* szErrorMsg);

// It is highly recommended that all Microsoft® SQL Server (7.0 
// and greater) extended stored procedure DLLs implement and export 
// __GetXpVersion. For more information see SQL Server 
// Books Online
ULONG __GetXpVersion()

{
    return ODS_VERSION;
}

SRVRETCODE xp_hello(SRV_PROC* pSrvProc)
{
    char        szText[15] = "Hello World!";
    BYTE        bType;
    long        cbMaxLen;
    long        cbActualLen;
    BOOL        fNull;

#ifdef _DEBUG
    // In a debug build, look up the data type name for assistance.
    DBCHAR*     pdbcDataType;
    int         cbDataType;
#endif

    // Count up the number of input parameters.  There should only be one.
    if (srv_rpcparams(pSrvProc) != 1)
        {
        // Send error message and return
        //
        printUsage (pSrvProc);
        return (XP_ERROR);
        }

    // Use srv_paraminfo to get data type and length information.
    if (srv_paraminfo(pSrvProc, 1, &bType, &cbMaxLen, &cbActualLen,
        NULL, &fNull) == FAIL)
        {
        printError (pSrvProc, "srv_paraminfo failed...");
         return (XP_ERROR);    
        }

    // Make sure first parameter is a return (OUTPUT) parameter
    if ((srv_paramstatus(pSrvProc, 1) & SRV_PARAMRETURN) == FAIL)
        {
        printUsage (pSrvProc);
        return (XP_ERROR);    
        }

    // Make sure first parameter is of char or varchar datatype
    if (bType != SRVBIGVARCHAR && bType != SRVBIGCHAR)
        {
        printUsage (pSrvProc);
        return (XP_ERROR);    
        }

    // Make sure first paramter is large enough to hold data
    if (cbMaxLen < (long)strlen(szText))
        {
        printError (pSrvProc, "output param max. length should be bigger");
        return (XP_ERROR);    
        }

    // Describe the results set 
//#define METHOD1
#ifdef METHOD1
    srv_describe(pSrvProc, 1, "Column 1", SRV_NULLTERM, bType,
            cbMaxLen, bType, strlen(szText), szText);
#else
    srv_describe(pSrvProc, 1, "Column 1", SRV_NULLTERM, bType,
            cbMaxLen, bType, 0, NULL);

    // Set the column's length
    if (srv_setcollen(pSrvProc, 1, strlen(szText)) == FAIL)
        {
        printError (pSrvProc, "srv_setcollen failed...");
        return (XP_ERROR);    
        }

    // Set the column's data
    if (srv_setcoldata(pSrvProc, 1, szText) == FAIL)
        {
        printError (pSrvProc, "srv_setcoldata failed...");
        return (XP_ERROR);    
        }

#endif //METHOD1

#ifdef _DEBUG
    // A debugging aid. Get the name of the data type of the parameter.
    pdbcDataType = srv_symbol(SRV_DATATYPE, (int) bType, &cbDataType);
#endif
    
    // Send a row to client
    if (srv_sendrow(pSrvProc) == FAIL)
        {
        printError (pSrvProc, "srv_sendrow failed...");
        return (XP_ERROR);    
        }

    // Set the output parameter
    if (FAIL == srv_paramsetoutput(pSrvProc, 1, szText, strlen(szText), FALSE))
        {
        printError (pSrvProc, "srv_paramsetoutput failed...");
        return (XP_ERROR);    
        }
        
    srv_senddone(pSrvProc, (SRV_DONE_COUNT | SRV_DONE_MORE), 0, 1);

    return (XP_NOERROR);
}

// send XP usage info to client
void printUsage (SRV_PROC *pSrvProc)
{
    // usage: exec xp_hello <@param1 output>
    // Example:
    // declare @txt varchar(33)
    // exec xp_hello @txt OUTPUT
    // select @txt

    srv_sendmsg(pSrvProc, SRV_MSG_ERROR, XP_HELLO_ERROR, SRV_INFO, 1,
            NULL, 0, (DBUSMALLINT) __LINE__, 
            "Usage: exec xp_hello <@param1 output>",
            SRV_NULLTERM);
    srv_senddone(pSrvProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
}

// send szErrorMsg to client
void printError (SRV_PROC *pSrvProc, CHAR* szErrorMsg)
{
    srv_sendmsg(pSrvProc, SRV_MSG_ERROR, XP_HELLO_ERROR, SRV_INFO, 1,
            NULL, 0, (DBUSMALLINT) __LINE__, 
            szErrorMsg,
            SRV_NULLTERM);

    srv_senddone(pSrvProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);
}
Xp_hello.def

The .def file used in the xp_hello sample exports the xp_hello function. This is in a sample only available if you select Dev Tools during setup.

LIBRARY XP_HELLO

DESCRIPTION   'Sample SQL Server Extended Stored Procedure DLL'

EXPORTS
   xp_hello
   __GetXpVersion