ADO.NET 3.5 Cookbook, 2nd Edition

10.13. Debugging a SQL Server Stored Procedure

Problem

Given an application that uses a SQL Server stored procedure that is causing errors, you need to debug the stored procedure.

Solution

Use Visual Studio .NET to debug SQL Server stored procedures (in both standalone mode and from managed code).

The solution provides a code sample to use during the discussion.

The C# code in Program.cs in the project DebugStoredProcedure is shown in Example 10-23.

Example 10-23. File: Program.cs for DebugStoredProcedure solution

using System;
using System.Data;
using System.Data.SqlClient; namespace DebugStoredProcedure { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "uspGetEmployeeManagers"; // Create the connection SqlConnection connection = new SqlConnection(sqlConnectString); // Create the stored procedure command SqlCommand command = new SqlCommand(sqlSelect, connection); command.CommandType = CommandType.StoredProcedure; command.Parameters.Add("@EmployeeID", SqlDbType.Int).Value = 1; // Create the data reader connection.Open(); SqlDataReader dr = command.ExecuteReader(); // Output the result set Console.WriteLine("Level\tID\tEmp Name\tMgrID\tMgrName"); while (dr.Read()) { Console.WriteLine("{0}\t{1}\t{2}, {3}\t{4}\t{5}, {6}", dr["RecursionLevel"], dr["EmployeeID"], dr["LastName"], dr["FirstName"], dr["ManagerID"], dr["ManagerLastName"], dr["ManagerFirstName"]); } ...