Professional SQL Server™ 2005 Programming

14.4. Creating Your Assembly-Based Stored Procedure

All right then; all the tough stuff is done (if you're looking for how to actually create the assembly that is the code for the stored proc, take a look back two sections). We have a compiled assembly, and we have uploaded it to SQL Server — it's time to put it to use.

To do this, we use the same CREATE PROCEDURE command we learned back in Chapter 11. The difference is that, in the place of T-SQL code, we reference our assembly. For review, the syntax looks like this:

CREATE PROCEDURE|PROC <sproc name>
   [<parameter name> [<schema>.]<data type> [VARYING] [= <default value>] [OUT [PUT]][,
    <parameter name> [<schema>.]<data type> [VARYING] [= <default value>] [OUT[PUT]][,
    ...
    ...
      ]]
[WITH
   RECOMPILE| ENCRYPTION | [EXECUTE AS { CALLER|SELF|OWNER|<'user name'>}]
[FOR REPLICATION]
AS
   <code> | EXTERNAL NAME <assembly name>.<assembly class>

Some of this we can ignore when doing assemblies. The key things are:

• We use the EXTERNAL NAME option instead of the <code> section we used in our main chapter on stored procedures. The EXTERNAL NAME is done in a format of

  <assembly name>.<class name>.<method name>

• We still need to define all parameters (in an order that matches the order our assembly method).

Now let's apply that to the assembly we created in the previous section:

CREATE PROC spCLRExample
    (
        @outval int = NULL OUTPUT
    )
AS EXTERNAL NAME ExampleProc.StoredProcedures.ExampleSP

It is not until this point that we actually have a stored procedure ...