Export (0) Print
Expand All

Walkthrough: Debugging a SQL CLR User-Defined Scalar Function

This topic applies to:

Edition

Visual Basic

C#

C++

Web Developer

Express

Topic does not applyTopic does not applyTopic does not applyTopic does not apply

Standard

Topic does not applyTopic does not applyTopic does not applyTopic does not apply

Pro and Team

Topic appliesTopic appliesTopic appliesTopic applies

Table legend:

Topic applies

Applies

Topic does not apply

Does not apply

Topic applies but command hidden by default

Command or commands hidden by default.

This example shows how to debug a SQL CLR User Defined Function (UDF). It creates a new SQL CLR User-Defined Function in the AdventureWorks sample database.

NoteNote:

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

To debug a SQL CLR user-defined scalar function

  1. In a new SQL Server project, establish a connection to the AdventureWorks sample database. For more information, see How to: Connect to a Database.

  2. Create a new function using the code from the first example section below, and name it OrderCount.cs. For more information, see How to: Develop with the SQL Server Project Type.

  3. Add a script that tests the function by including it in a SELECT statement. In Solution Explorer, right-click the TestScripts directory, click Add Test Script, and insert the code from the second Example section below. Save the file with the name CallOrderCount.sql. Right-click the file name, and choose Set as Default Debug Script.

  4. Place a breakpoint in OrderCount.cson the line that instantiates a SqlCommand, and then on the Debug menu, click Start to compile, deploy, and unit-test the project. When the instruction pointer, designated by a yellow arrow, appears on the breakpoint, you are debugging your function.

  5. Try out different debugging features.

    1. Step past the statement instantiating the SqlCommand using Step Into from the Debug menu.

    2. In the Locals window, open the variable sqlComm, which is a SqlCommand, and examine its members.

    3. Click Step Into on the Debug menu to step one line in the function. Note that the member sqlComm.CommandText has been instantiated.

    4. In the Text Editor, drag sqlComm to any location in the Watch window. The variable is now added to the list of watched variables.

    5. Choose Step Into again, and note that a new window labeled Dynamic T-SQL opens, displaying the SQL statement that is about to be executed.

    6. Choose Step Into to execute the statement and return to the function.

    7. Press Continue again, and note the second Dynamic T-SQL window that opens, showing the code that returns the value of the function.

    8. Press Continue again to finish debugging the function.

This is the code required to create the function.

using System;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
 
 
public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static int OrderCount()
    {
        using (SqlConnection conn = new SqlConnection("context connection=true"))
            {
                  conn.Open();
                  SqlCommand cmd = new SqlCommand();
                  cmd.Connection = conn;
                  cmd.CommandText = "SELECT 42";
                  
                  //cmd = new SqlCommand("SELECT 42",conn);
 
                  int result = (int)cmd.ExecuteScalar();
                  return result;
            }
    }
};

This is the test script that calls the function.

SELECT dbo.OrderCount()

Community Additions

ADD
Show:
© 2014 Microsoft