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.
-- 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)
#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);
}
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