When the assembly is created, the DLL is copied into the target SQL Server database and the assembly is registered. The following code illustrates creating the MyCLRProc stored procedure that uses the MyCLRDLL assembly:
CREATE PROCEDURE MyCLRProc AS EXTERNAL NAME MyCLRDLL.StoredProcedures.MyCLRProc
The EXTERNAL NAME clause is new to SQL Server 2005. Here the EXTERNAL NAME clause specifies that the stored procedure MyCLRProc will be created using a .SQL Server assembly. The DLL that is encapsulated in the SQL Server assembly can contain multiple classes and methods; the EXTERNAL NAME statement uses the following syntax to identify the correct class and method to use from the assembly:
In the case of the preceding example, the registered assembly is named MyCLRDLL. The class within the assembly is StoredProcedures, and the method within that class that will be executed is MyCLRProc.
Specific examples showing how you actually go about creating a new managed code project with Visual Studio 2005 are presented in the next section.
Creating CLR database objects
The preceding section presented an overview of the process
NOTE: The creation of SQL Server projects is supported in Visual Studio 2005 Professional Edition and higher. It is not present in Visual Studio Standard Edition or the earlier releases of Visual Studio.
The previous tip is from "Developing CLR database objects: 10 tips in 10 minutes," excerpted from Chapter 3 of the book "Microsoft SQL Server 2005: A Developer's Guide" written by Michael Otey and Denielle Otey, courtesy of McGraw-Hill Publishing.
Developing CLR database objects
Tip 1: CLR architecture
Tip 2: CLR assemblies in SQL Server 2005
Tip 3: Creating CLR database objects
Tip 4: CLR stored procedures
Tip 5: User-Defined Functions
Tip 6: CLR triggers
Tip 7: User-Defined Types
Tip 8: CLR aggregates
Tip 9: Debugging CLR database objects
Tip 10: .NET database object security
This was first published in May 2007