A Simple Walkthrough for deploying a SQLCLR Stored Procedure (by Brad)

Walkthrough:  Simple deployment of SQLCLR Visual Basic Stored Procedure


The Yukon release of SQL Server allows functions, procedures and triggers to be written in any of the .Net languages. User code can access data from the local or other SQL servers using the Sql Programming Model.


SQL Server Yukon includes a managed provider defined in System.Data.SqlServer that runs in the process space of the server and that users writing code in any of the .Net languages can use to access data.


This document describes a simple example of a Visual Basic SQL Project which contains a simple stored procedure using the data access managed provider that runs inside the server.


In order to complete this walkthrough, you will need:


  • Access to a SQL Server 2005 server with the AdventureWorks SQL Server sample database.
  • CREATE ASSEMBLY and CREATE FUNCTION permission on the server or an administrator login.

The walkthrough is split into a number of pieces:


  • Creating a Visual Basic SQL Project w/ a connection to the SQL Server 2005 database.
  • Add a Stored Procedure to the project.
  • Edit the code of the Stored Procedure to make use of the In-Proc Managed Provider
  • Deploy the function to the SQL Server 2005 database.
  • Execute the function from Visual Studio Server Explorer.

Creating the Project and Function

The first step is to create a VB SQL Server or Visual C# Project


To create the project and function


  1. If you already have a solution open, select Close Solution from the File menu.
  2. From the File menu, point to New, and then click Project. 
  3. In the New Project dialog, choose Visual Basic or Visual C# Projects in the Project Types pane.
  4. In the Templates pane, choose the SQL Server Template.  For Visual C++, choose SQL Server Class Library from the .Net node.
  5. Assign a unique name to the project, and click OK.

Visual Studio will create a new project and display a dialog to choose the SQL Server to which the Assembly will be deployed.

Note:  If there hasn’t been a connection previously established to a SQL Server 2005 server, Visual Studio will first prompt the user w/ the Add Connection dialog.


In this dialog, the user will choose an existing connection to a SQL Server 2005 sever or create a new connection by selecting the Add Reference button.


  1. Select OK after highlighting the correct SQL Server connection

This completes the creation of the SQL Server project


Add a Stored Procedure to the project


Now that the project has been created, a stored procedure can be added to the project.


  1. Select Project from the Main Menu in the Visual Studio environment.
  2. From the Project menu, select Add Stored Procedure.
  3. The Add New Item dialog should now be visible.  Choose the template Stored Procedure from the list of installed Visual Studio Templates.  
  4. Type in a valid name for the procedure, and select Add.

 The newly created code file will contain the following Visual Basic code.

Imports System

Imports System.Data

Imports System.Data.Sql

Imports System.Data.SqlServer

Imports System.Data.SqlTypes


Partial Public Class StoredProcedures

    <SqlProcedure()> _

    Public Shared Sub  StoredProcedure1 ()

        ' Add your code here

    End Sub

End Class


The previous code snippet is the basic template from which a stored procdured can be created.  Now, change the code in the stored procedure to the following:


    Public Shared Sub  StoredProcedure1 ()

        Dim cmd As SqlCommand


        cmd = SqlContext.GetConnection.CreateCommand()

        cmd.CommandText = "UPDATE Person.Contact SET" & _

                          "MiddleName = 'Jack' WHERE ContactID = 1"

        cmd.CommandType = CommandType.Text



    End Sub


This example performs a simple update to the Contacts table in the AdventureWorks database.  This is accomplished by creating a command object from the SqlContext.Connection object.  In effect, what is happening is that the procedure is running in the “context” of the connection that it is being called from.  The user is not required, nor allowed, to create a completely new connection to perform this operation.



Deploy the Stored Procedure to the SQL Server 2005 database


So far, the project with a connection to the SQL Server 2005 database is created, and the stored procedure is created.  The project is now ready to be deployed to the server.


  1. Select the Build Menu from the Main menu in the Visual Studio environment.
  2. Now, select Deploy Solution from the Build menu. 

The Build Output pain should contain information similar to the following indicating a successful deployment


------ Build started: Project: SqlServerProject1, Configuration: Debug Any CPU ------

------ Deploy started: Project: SqlServerProject1, Configuration: Debug Any CPU ------

Deploying file: SqlServerProject1.dll, Path: D:\school\SqlServerProject1\SqlServerProject1\obj\Debug\SqlServerProject1.dll ...

Deploying file: SqlServerProject1.pdb, Path: D:\school\SqlServerProject1\SqlServerProject1\obj\Debug\SqlServerProject1.pdb ...

Deploying file: StoredProcedure1.vb, Path: D:\school\SqlServerProject1\SqlServerProject1\StoredProcedure1.vb ...

Deploying file: My Project\MyResources.resx, Path: D:\school\SqlServerProject1\SqlServerProject1\My Project\MyResources.resx ...

Deploying file: SqlServerProject1.vbproj, Path: D:\school\SqlServerProject1\SqlServerProject1\SqlServerProject1.vbproj ...

========== Build: 1 succeeded, 0 failed, 0 up-to-date, 0 skipped ==========

========== Deploy: 1 succeeded, 0 failed, 0 skipped ==========


For another method of verification, the user may query the system tables to check for a successful deployment:

SELECT * FROM sys.assemblies

SELECT * FROM sys.assemby_modules



Execute the deployed Stored Procedure


Now that the procedure is deployed to the server, users can execute the procedure from Server Explorer to verify it is performing as desired.


  1. Select the connection to the SQL Server 2005 in the Server Explorer window in Visual Studio environment.  
  2. Expand the connection to the Stored Procedures node and view the procedure that was just deployed.

Note:  The Stored Procedures node may need to be refreshed in order to display what was just deployed.


  1. Highlight StoredProcedure1, and select the Context Menu by “right-clicking” the mouse.
  2. Select Execute from the Context menu
  3. View the contents of the Output pane.  In this case, the output will indicate that no records were affected.  This is due to the output reported by the stored procedure.

 Note:  Ensure Database output is selected in the Output pane to view the results of the execution of the deployed function.  There is a bug currently whereby the number of rows affected is not returned correctly.  Verification of the operation can be accomplished by re-querying the database.



This walkthrough has illustrated the basic steps involved in creating, deploying, and executing a Visual Basic, Visual C# SQL Server Project.  The user could make a number of enhancements to this project.

  • Modify the code in the function to make calculations on data in the user’s database.   
  • Reference other deployed Assemblies to use their functionality in this assembly.
Comments (9)

  1. Jason Shantz says:

    What does the ‘Deploy Solution’ command do behind the scenes? Deploying from VS is great when developing, but in production I’ll need a scripted approach of some kind.

    Also, which files must be deployed and which are optional?


  2. G. Man says:

    A great introductory article and I second Jason’s questions.

    I also want to see a detailed discussion not on HOW you do this, but WHY. The code same you posted is traditionally part of the "data layer" of a web application. What are exactly the benefits and drawbacks to physically moving the code to the database server itself?

  3. A Simple Walkthrough for deploying a SQLCLR Stored Procedure

  4. Eric Newton says:

    G Man: it would take a lot longer article to detail benefits versus drawbacks of the idea… ie, i’ve seen requests for asp.net inside the procedure… um, hello? (not to G Man)

    Another stipulation that needs to be mentioned is that CLR sprocs REQUIRE the static keyword. Since sprocs cant have state… its a good way to guarantee no state. (Good job)

  5. Doro says:

    thank you and what about parametrised SP

  6. Walkthrough of building a SQLCLR stored proc with VB

  7. Brad says:

    1. As expected, it is possible to deploy manually. If you have any SQL Server 2005 documentation, the details should be contained there. The Deploy command essentially is as follows:

    CREATE ASSEMBLY statement:

    CREATE ASSEMBLY [ assembly_name ]

    [ AUTHORIZATION owner_name ]

    {FROM { client_assembly_specifier |

    assembly_bits [,…]



    [ WITH option_list ]

    option_list ::=

    option_spec [,…]






    { varbinary_literal | varbinary_expression }_


    DROP ASSEMBLY assembly_name [,…]

    [WITH drop_option]


    The CREATE PROCEDURE (and CREATE FUNCTION) statments have been altered to support SQLCLR. The following is the syntax for creating a procedure:



    [ WITH {proc_attribute} [,…] ]

    [ AS ]



    proc_name ( [ {param_declaration} [,… ] ] )


    [ proc_schema_name. ]proc_object_name


    @param_name param_type [ = default_value ]

    [ COLLATE collation_name ] // might be cut



    { [BEGIN] procedure_body [END] }

    | { EXTERNAL NAME method_specifier }





    /* as defined in CREATE FUNCTION */



    | EXECUTE AS { SELF | CALLER | USER = user_name }

    2. The only file that is required to be deployed is the assembly. That said, other files may be deployed with the assembly. Visual Studio, by default, deploys the code file w/ the assembly. This facilitates SQLCLR debugging. There is an option on the property page of the SQLCLR project to turn off deploying of code files as well.

  8. roy ashbrook says:

    Stuff I&#39;ve been intending to post a meaningful post about, but haven&#39;t: If you have ever wondered

Skip to main content