Here is an example Extended Stored Procedure (xp) using SQL Servers Open Data Services (ODS). Some really great stuff. Why? Because it allows you to use a math library like CEPHES or GSL and return values right back into T-SQL. If you compile with Jacob Navia's compiler (LCC) you have easy access to math libraries. Visual Studio, no such luck. In Visual Studio though, you have an easy framework for xp's to use. In LCC you have to find the SQL Server opends60.lib file and convert it for use with LCC and also you need to find the srv.h file and copy it into the header files folder. There are a few other things also. It isn't tough but it is a little more than copying and pasting like in Visual Studio. LCC has great documentation so it is very worthwhile taking a look at. And did I also mention that it has a math library!

An xp written in C has performance advantages over the newer Common Language Runtime (CLR) bound languages and you have access to difficult to write math functions. This allows you to access functions from T-SQL that are not already present, return the values into a stored procedure which then makes it easy to hook a UI to the results for easy analysis.

The students t function is an interesting function to take a look at. You can find the source code on the internet if you search for the CEPHES math library. It is a difficult function to write and benefits from the performance of C code. By taking a look at this function you also gain an appreciation and insight of some excellent programming. Complementary to W. S. Gosset

Here is a simple example for Visual Studio but without the intended function call to students t function.

Open Visual Studio. Go to File>New>Project, pick C++ and in the dialog box pick Extended Stored Procedure dll. In the name field enter xp_StudentsTest. Change the path to one that works for you. Click OK. Click Finish. Now in the Solution Explorer on the right hand side of the screen click on the proc.cpp file. Delete all of the text from this file and copy the entire C file text from below into it. Go to the Build menu and click Build Solution. This will build a bunch of files in the xp_StudentsTest directory. In the Debug folder there will be a file called xp_StudentsTest.dll. Select this and copy it in to the Binn folder under your SQL Server install directory. I have two Binn folders on my install. Copy the dll into the Binn folder under the directory labeled MSSQL&<NameOfServer>. Now open the SQL script(text below) in Query Analyzer and run it!

OK, here are some warnings. There isn't much error handling in the xp for the sake of simplicity. Once you drop the xp stop the SQL Server service and then delete the dll. To debug C code is a little tricky here. I just have some simple return messages. Also you don't have to compile under the debug setting. I used Version 7 of Visual Studio and also SQL Server 2000 on a Windows 2000 Server. If you use a different version of Visual Studio the dialog boxes might be different. Good luck and please use a test computer!

By C. Eric Cashon

--SQL Script for Query Analyzer.

USE master

GO

sp_addextendedproc 'xp_students_t', 'xp_StudentsTest.dll'

GO

DECLARE @Number1 float

DECLARE @Number2 int

DECLARE @Number3 float

SET @Number2 = 3

SET @Number3 = 34.89723

--Student's t prototype from LCC statistics library.

--long double students_t(int df, long double t)

--This example does not return this value!!!

--It shows how it can be done. All it does is multiply

--two input values together.

EXEC xp_students_t @Number1 OUTPUT, @Number2, @Number3

PRINT @Number1

PRINT @Number2

PRINT @Number3

GO

--sp_dropextendedproc 'xp_students_t'

//C code text for Visual Studio.

#include <stdafx.h>

#include <windows.h>

#include <srv.h>

//Return codes

#define XP_NOERROR 0

#define XP_ERROR 1

#ifdef __cplusplus

extern "C" {

#endif

RETCODE __declspec(dllexport) xp_students_t(SRV_PROC *srvproc);

#ifdef __cplusplus

}

#endif

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

ULONG __declspec(dllexport) __GetXpVersion()

{

return ODS_VERSION;

}

//Export the sample function.

SRVRETCODE __declspec(dllexport) xp_students_t

(

SRV_PROC* pSrvProc

)

{

int nParams;

int nParam = 1;

SRVRETCODE rc = XP_NOERROR;

double dOutput1;

double * pOutput1;

int * pInput1;

double * pInput2;

// Need two input arguments for Students t function and one output.

nParams = srv_rpcparams(pSrvProc);

if (nParams != 3)

{

printError (pSrvProc, "1 failed...");

rc = XP_ERROR;

}

// Make sure first parameter is a return (OUTPUT) parameter

if ((srv_paramstatus(pSrvProc, 1) & SRV_PARAMRETURN) == FAIL)

{

printError (pSrvProc, "2 failed...");

rc = XP_ERROR;

}

//Get a pointer to the 2nd argument.

if (rc == XP_NOERROR)

{

pInput1 = ((int*)srv_paramdata(pSrvProc, 2));

}

//Get a pointer to the 3rd argument.

if (rc == XP_NOERROR)

{

pInput2 = ((double*)srv_paramdata(pSrvProc, 3));

}

//Math library function location. For an example multiply

//the two input values together. This is where the students t

//function would be.

if (rc == XP_NOERROR)

{

dOutput1 = ((float)*pInput1)* (*pInput2);

pOutput1 = &dOutput1;

}

// Set the output parameter

if (rc == XP_NOERROR)

{

//Send the output back to T-SQL.

if (FAIL == srv_paramset(pSrvProc, 1, pOutput1, sizeof(dOutput1)))

{

printError (pSrvProc, "3 failed...");

return (XP_ERROR);

}

}

// Indicate that we're done.

if (rc == XP_NOERROR)

{

srv_senddone(pSrvProc, (SRV_DONE_COUNT | SRV_DONE_MORE), 0, 0);

}

else if (rc == XP_ERROR)

{

printError (pSrvProc, "XP encountered an error.");

}

return (rc);

}

//Send error message to client. Useful for basic debugging.

void printError (SRV_PROC *pSrvProc, CHAR* pErrorMsg)

{

srv_sendmsg(pSrvProc, SRV_MSG_ERROR, XP_ERROR, SRV_INFO, 1,

NULL, 0, (DBUSMALLINT) __LINE__,

pErrorMsg,

SRV_NULLTERM);

srv_senddone(pSrvProc, (SRV_DONE_ERROR | SRV_DONE_MORE), 0, 0);

}