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

-- Add xp_hello to SQL Server

sp_addextendedproc 'xp_hello', 'xp_hello.dll'

GO

  

-- Call xp_hello.

DECLARE @txt varchar(33)

EXEC xp_hello @txt OUTPUT

SELECT @txt AS Output_Parameter

GO

  

-- EXPECTED RESULTS

-- Column 1:

-- ---------

-- Hello World!

  

-- (1 row(s) affected)

  

-- Output_Parameter

-- ----------------

-- Hello World!

  

-- (1 row(s) affected)

  

C Source Code

#include <srv.h>

  

// Macros -- return codes

#define XP_NOERROR      0

#define XP_ERROR        1

  

SRVRETCODE xp_hello (SRV_PROC* pSrvProc)

{

     char            szText[15] = "Hello World!";

    BYTE        bType;

    long        cbMaxLen;

    long        cbActualLen;

    BOOL        fNull;

    int            ret;

    int         paramCount;

  

    // Count the number of parameters.

    paramCount = srv_rpcparams(pSrvProc);

  

    if (1 != paramCount)

        return (XP_ERROR);

  

    // Use srv_paraminfo to get data, type, and length information

    // of the parameter.

    ret = srv_paraminfo(pSrvProc, 1, &bType, &cbMaxLen, &cbActualLen,

        NULL, &fNull);

  

    // Set the output parameter.

    if (cbMaxLen < strlen(szText))

        return XP_ERROR;

    else

        ret = srv_paramsetoutput(pSrvProc, 1, szText, strlen(szText), FALSE);

  

    // Describe one row with one column of type BIGVARCHAR

    ret = srv_describe(pSrvProc, 1, "Column 1", SRV_NULLTERM, SRVBIGVARCHAR,

            strlen(szText), SRVBIGVARCHAR, strlen(szText), szText);

    

    // Send the row.

    ret = srv_sendrow(pSrvProc);

  

        

    // Send results completion message.

    ret = srv_senddone(pSrvProc, (SRV_DONE_COUNT | SRV_DONE_MORE), 0, 1);

  

    return (XP_NOERROR);

}

  

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

  

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.