What is an extended stored procedure?
Extended stored procedures allow you to create your own external routines using programming languages like C. For users, the extended stored procedure and common stored procedure have the same execution method. Parameters can be passed to the extended stored procedure. The extended stored procedure can return results or status. Extended stored procedures can be used to extend the functions of Microsoft & reg; SQL Server & #8482; 2000.
The extended stored procedure is a dynamic link library (DLL) that SQL Server can dynamically load and execute ). The extended storage process runs directly in the address space of SQL Server, and uses SQL Server Open Data Service (ODS) API programming.
After the extended stored procedure is compiled, members of the fixed Server role sysadmin can register the Extended Stored Procedure in SQL Server and grant other users the permission to execute the procedure. The extended stored procedure can only be added to the master database.
Use C # To write extended stored procedures
The following is a simple example to demonstrate how to use C # to compile an extended stored procedure.
First, create a simple C # class library file:
// C # file: Csserver. cs
Using System;
Using System. Runtime. InteropServices;
Using System. Reflection;
Using System. Runtime. CompilerServices;
[Assembly: AssemblyTitle ("CSServer")]
[Assembly: AssemblyDescription ("Test SQL. NET interop")]
[Assembly: AssemblyVersion ("1.0.0.1")]
[Assembly: AssemblyDelaySign (false)]
[Assembly: AssemblyKeyFile ("MyKey. snk")]
Namespace SQLInterop {
Public interface ITest {
String SayHello ();
}
[ClassInterface (ClassInterfaceType. AutoDual)]
Public class CsharpHelper: ITest {
Public string SayHello (){
Return "Hello from CSharp ";
}
}
}
Then create a strong-name key file for the class library with sn-k and compile it.
Sn-k MyKey. snk
Csc/t: library Csserver. cs
Register the Class Library:
Regasm/tlb: Csserver. tlb csserver. dll/codebase
In this way, the extended storage process is compiled and registered. Let's test the effect in SQL server.
T-SQL stored proc.
DECLARE @ object int
DECLARE @ hr int
DECLARE @ property varchar (255)
DECLARE @ return varchar (255)
DECLARE @ src varchar (255), @ desc varchar (255)
-- Create an object instance.
EXEC @ hr = sp_OACreate SQLInterop. CsharpHelper, @ object OUT
IF @ hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ object, @ src OUT, @ desc OUT
SELECT hr = convert (varbinary (4), @ hr), Source = @ src, Description = @ desc
RETURN
END
-- Call the object method.
EXEC @ hr = sp_OAMethod @ object, SayHello, @ return OUT
IF @ hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ object, @ src OUT, @ desc OUT
SELECT hr = convert (varbinary (4), @ hr), Source = @ src, Description = @ desc
RETURN
END
PRINT @ return
-- Destroys an object instance.
EXEC @ hr = sp_OADestroy @ object
IF @ hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ object, @ src OUT, @ desc OUT
SELECT hr = convert (varbinary (4), @ hr), Source = @ src, Description = @ desc
RETURN
END