Structure of a CLR Stored Procedure

The CLR database objects that were created and deployed in the previous chapter were stored procedures:

 using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using Microsoft.SqlServer.Server; public partial class StoredProcedures {     [SqlProcedure]     public static void ap_First()     {      SqlContext.Pipe.Send("Hello world!\n");     } }; 

Or in Visual Basic:

 Imports   System Imports   System.Data Imports   System.Data.SqlClient Imports   System.Data.SqlTypes Imports  Microsoft.SqlServer.Server Partial Public Class StoredProcedures     <SqlProcedure()> _     Public Shared Sub  ap_First ()     SqlContext.Pipe.Send("Hello world!\n"}    End Sub End Class 

The first part of the file is a set of references to namespaces that will be used later in the code. The second part is a class (or actually a part of a class) that contains a method that will be promoted to a database object.

The class is defined as public to denote that other classes can instantiate objects of this type (and use them).

In .NET Framework 2.0, the CLR class does not have to be defined in a single file. The idea is that the source code for every database object should be in a separate file (to be easier to manage). Therefore, Visual Studio names the file and method using the same name. You just need to declare it as partial (as in public partial class in C# or as Partial Public Class in Visual Basic).

The method is preceded by the [Microsoft.SqlServer.Server.SqlProcedure] attribute in C# (or <Microsoft.SqlServer.Server.SqlProcedure()> in Visual Basic) that notifies Visual Studio and the compiler how to treat the method and prepare it for deployment in SQL Server. Since the Microsoft.SqlServer.Server namespace is referenced in the using (or Imports in Visual Basic) statement, it is sufficient to put only [SqlProcedure] as an attribute of the method.

Methods that will become database objects must be declared as public static in C# or Public Shared in Visual Basic. The keyword public means that they are accessible from outside and static (or Shared) means that they behave like procedures (not like methods of objects that have state). The keyword void is in the position that declares an output value of the method (stored procedure). The keyword void declares that the method does not return anything. If you want it to return 4-byte integers, you should declare it as int. In Visual Basic, subroutines (Sub) do not return values, while functions (Func) do.