在存储过程中调用外部的动态连接库

CREATE PROCEDURE sp_testdll AS

exec sp_addextendedproc 'SetFileName', 'storeproc.dll' --声明函数
exec sp_addextendedproc 'addLine', 'storeproc.dll'  

declare @szFileName varchar(200)     
declare @szText varchar(200)
declare @rt int

Select @szFileName = 'c:\welcome.txt'

EXEC @rt = SetFileName @szFileName --调用SetFileName函数，参数为--szFileName;
if @rt = 0
begin
select @szText = 'welcome 01'
Exec @rt = addLine @szText     --调用addLine
select @szText = 'welcome 02'
Exec @rt = addLine @szText

end
exec sp_dropextendedproc 'SetFileName'
exec sp_dropextendedproc 'addLine'

dbcc SetFileName(free)
dbcc addLine(free)

动态连接库的实现：这种动态连接库和普通的有所不同。该动态连接库要放入SQL的执行目录下，或直接放到Window的System32目录下,并重起SQL-Server #include <windows.h>
#include <srv.h>   //要加入这个.h文件

#define XP_NOERROR      0
#define XP_ERROR        1

#ifndef _DEBUG
#define _DEBUG
#endif

char szFileName[MAX_PATH+1];

void WriteInfo(const char * str);

extern "C" SRVRETCODE WINAPI SetFileName(SRV_PROC* pSrvProc)
{
 WriteInfo("SetFileName start");
 int paramCount = srv_rpcparams(pSrvProc);
 if (paramCount != 1){
  WriteInfo("Param Err start");
  return XP_ERROR;
 }

 BYTE  bType;
 unsigned long cbMaxLen;
 unsigned long cbActualLen;
 BOOL  fNull;

 int ret = srv_paraminfo(pSrvProc, 1, &bType, &cbMaxLen, &cbActualLen,
          NULL, &fNull);
 if (cbActualLen){
  ZeroMemory(szFileName, MAX_PATH+1);
  memcpy(szFileName, srv_paramdata(pSrvProc, 1), cbActualLen);
  WriteInfo("Set filename ok");
  return (XP_NOERROR);
 }
 else {
  WriteInfo("Set filename param failed");
  return XP_ERROR;
 }
}

extern "C" SRVRETCODE WINAPI addLine(SRV_PROC* pSrvProc)
{
 WriteInfo("addline start");
 int paramCount = srv_rpcparams(pSrvProc);
 if (paramCount != 1){
  WriteInfo("addline param err");
  return XP_ERROR;
 }

 BYTE         bType;
 unsigned long cbMaxLen;
 unsigned long cbActualLen;
 BOOL  fNull;
 bool  rt = false;

 int ret = srv_paraminfo(pSrvProc, 1, &bType, &cbMaxLen, &cbActualLen,
          NULL, &fNull);

 if (cbActualLen){
  int n;
  char srt[3] = {0x0d, 0x0a, 0};

  char * c = new char[cbActualLen + 3];
  if (!c)return XP_ERROR;

  ZeroMemory(c, cbActualLen + 3);
  memcpy(c, srv_paramdata(pSrvProc, 1), cbActualLen);
  memcpy(c+cbActualLen, srt, 3);

  HANDLE hf = CreateFile(szFileName, GENERIC_WRITE, FILE_SHARE_WRITE|FILE_SHARE_READ, NULL,
             OPEN_ALWAYS, 0, NULL);
  if (hf == INVALID_HANDLE_VALUE){
   WriteInfo("addline create file err ");
   delete []c;
   return XP_ERROR;
  }

  WriteInfo("addline create file ok ");
  DWORD dwWt;
  n = strlen(c);
  SetFilePointer(hf, 0, NULL, FILE_END);
  if (WriteFile(hf, c, n, &dwWt, NULL) && dwWt == n)
  {
   WriteInfo("addline write file ok ");
   rt = true;
  }
  delete []c;
  CloseHandle(hf);
 }
 return rt ? XP_NOERROR:XP_ERROR;
}

inline void WriteInfo(const char * str){
    #ifdef _DEBUG
 char srt[3] = {0x0d, 0x0a, 0};
 HANDLE hf = CreateFile("c:\\storeproc.log", GENERIC_WRITE, FILE_SHARE_WRITE|FILE_SHARE_READ, NULL,
             OPEN_ALWAYS, 0, NULL);
 if (hf != INVALID_HANDLE_VALUE){
  SetFilePointer(hf, 0, NULL, FILE_END);
  DWORD dwWt;
  WriteFile(hf, str, strlen(str), &dwWt, NULL);
  WriteFile(hf, srt, strlen(srt), &dwWt, NULL);
  CloseHandle(hf);
 }
 else {
  MessageBox(NULL, "Write info err", "Message", MB_OK|MB_ICONINFORMATION);
 }
 #endif
}

BOOL WINAPI DllMain(HINSTANCE hinstDLL,DWORD fdwReason,LPVOID lpReserved)
{
 return TRUE;
}

编译完成后，把动态链接库放到WINNT/System32目录下,启动SQL Server。我们可以打开SQL Server Query Analyzer调用存储过程sp_testdll以测试其运行是否正确。

具体可参考SQL-Server的在线帮助。
笔者环境：win2000 professional + SQL-Server7.0(2000也可)
VC6.0+SP5+Platform SDK 20001.8