After the SQL Server assembly is created, you can then use SQL Server Management Studio to execute a T-SQL CREATE PROCEDURE, CREATE TRIGGER, CREATE FUNCTION, CREATE TYPE, or CREATE AGGREGATE statement that uses the EXTERNAL NAME clause to point to the assembly that you created earlier.
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:
Assembly Name.ClassName.MethodName
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 along with some example manual CLR database object creation steps to help you better understand the creation and deployment process for CLR database objects. However, while it's possible to create CLR database objects manually, that's definitely not the most productive method. The Visual Studio 2005 Professional, Enterprise, and Team System Editions all have tools that help create CLR database objects as well as deploy and debug them. In the next part of this chapter you'll see how to create each of the new CLR database objects using Visual Studio 2005.
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
Home: Introduction
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
