How to: Develop with the SQL Server Project Type

This topic applies to:

Edition	Visual Basic	C#	C++	Web Developer
Express
Standard
Pro and Team

Table legend:

	Applies
	Does not apply
	Command or commands hidden by default

The following procedure describes how to create SQL database objects such as stored procedures or functions using the Visual Studio SQL Server project type. For a new project, often you create stored procedures first, since that is the component that is called directly by applications using the database.

The second procedure in this topic explains how to test and debug by running a test script against an object that has already been created. In this scenario you have already created the object and run a simple test against it. Now you have a more complex set of tests to run against the already existing object. As you add tests, you may discover bugs, in which case you go back to the first procedure to recompile your object.

To create and debug a SQL object using Visual Studio

1. Start Visual Studio, and create a new SQL Server project.

2. In Server Explorer, expand the Data Connections node. If the database to which you are adding the object does not appear, establish a connection. For more information, see How to: Connect to a Database.

3. Verify SQL Debugging is enabled for the connection. For more information, see How to: Enable CLR Debugging For a Connection.

4. If you want to create a T-SQL object, do the following:

   1. Expand the database node, and right-click the node type that corresponds to the object you are creating, and then on the shortcut menu, select New [Object Type].

      A template for the new object opens in the Text Editor pane.

   2. Fill in the template with the code that creates the object.

   3. On the File menu, click Save, and fill in a name for the object.

      The script is run on the server against the database, the object is created, and its name is added under the node associated with its object type in Server Explorer.

   -or-

   If you want to create a SQL CLR object, do the following:

   1. On the File menu, click Project, and then click Add [object type]. The Add New Item dialog box appears. Type a name in the text box, and then click Add.

   2. Fill in the template with the code for your object.

   3. In Solution Explorer, under the TestScripts directory, double-click the Test.sql file to open it in the Text Editor pane, and insert code that will test your object. This code will be written in T-SQL. For more information, see SQL CLR Database Debugging. You can also create and save separate test scripts with different file names, in which case you select the one to run by right-clicking the file name, and choosing Set as Default Debug Script.

5. Place breakpoints in objects by double-clicking the object name in Server Explorer, and then clicking in the left margin of the Text Editor on the lines of code where you want the breakpoint.

6. To compile, deploy, and test the code, press F5.

   A message is displayed in the Output window stating whether the object was successfully deployed to the SQL Server database. The script in the Test.sql file, or whichever file was made the default debug script, will run, and if any breakpoints you have set are hit, you can debug the object.

7. Try out different debugging features.

   1. On the Debug menu, click Windows, and then choose Locals to open the Locals window.

      Notice that the parameters and local variables are displayed in the Locals window with their corresponding values. You can edit the values of the variables in the Locals window as the stored procedure runs. For more information, see How to: Use Debugger Variable Windows.

      Note

      The server may not reflect changes to values of variables in the debugger windows. For more information, see SQL Debugging Limitations.

   2. Press F10 to step one line in the stored procedure.

      Notice that if the object changes the value of a variable, you will see in the Locals window its value now displayed in red, indicating it has changed.

   3. On the Debug menu, click Windows, and then click Watch to open the Watch window. For more information, see How to: Use Debugger Variable Windows.

   4. In the Text Editor, double-click a variable to select it. Drag it to any spot on the Watch window. The variable is now added to the list of watched variables.

      Note

      You can edit the values of variables in the Watch window as well.

   5. In the Text Editor, right-click a command, and on the shortcut menu, click Insert Breakpoint.

   6. On the Debug menu, click Continue.

      The debugger stops on the breakpoint that you just created.

8. Click Continue again to finish debugging the stored procedure.

9. View the results in the Output window, and select Show output from: Database Output.

Running a test script in a Visual Studio SQL Server project

1. Open Visual Studio.

2. If you have not already done so, establish a connection to the database.

3. Verify that SQL Debugging is enabled for the connection. For more information, see How to: Enable CLR Debugging For a Connection.

4. Double-click the object name in Server Explorer, and then click in the left margin of the Text Editor on the lines of code where you want the breakpoint.

5. In Solution Explorer, double-click the test script file name under the TestScripts folder, which will open the file in the Text Editor window. Add any additional tests and save the file.

6. In Solution Explorer, right-click the script file name under the TestScripts folder, and click Debug script.

7. The code for the object appears, with a yellow arrow in the left gray margin indicating the statement about to be executed. You can now use most of the familiar debugger features. For more information, see Debugger Roadmap.

See Also

Other Resources

Creating SQL Server 2005 Objects in Managed Code

Overview of Visual Database Tools

Debugging SQL Database Objects