Creating CLR Stored Procedures

  • Article

In SQL Server 2005, you can create a database object inside an instance of SQL Server that is programmed in an assembly created in the Microsoft .NET Framework common language runtime (CLR). Database objects that can leverage the rich programming model provided by the CLR include triggers, stored procedures, functions, aggregate functions, and types.

Creating a CLR stored procedure in SQL Server involves the following steps:

  • Define the stored procedure as a static method of a class in a language supported by the .NET Framework. For more information about how to program CLR stored procedures, see CLR Stored Procedures. Then, compile the class to build an assembly in the .NET Framework by using the appropriate language compiler.
  • Register the assembly in SQL Server by using the CREATE ASSEMBLY statement. For more information about how to work with assemblies in SQL Server, see Assemblies.
  • Create the stored procedure that references the registered assembly by using the CREATE PROCEDURE statement.

Note

Deploying a SQL Server Project in Microsoft Visual Studio registers an assembly in the database that was specified for the project. Deploying the project also creates CLR stored procedures in the database for all methods that are annotated with the SqlProcedure attribute. For more information, see Deploying CLR Database Objects.

Note

The ability of SQL Server to execute CLR code is off by default. You can create, alter, and drop database objects that reference managed code modules, but these references will not execute in SQL Server unless the clr enabled Option is enabled by using sp_configure (Transact-SQL).

To create, modify, or drop an assembly

To create a CLR Stored Procedure

See Also

Other Resources

Implementing Stored Procedures
Database Engine .NET Framework Programming

Help and Information

Getting SQL Server 2005 Assistance