Debug a Stored Procedure in Visual Studio

To debug a stored procedure (sproc) in Visual Studio follow the below steps

1. Start Visual Studio and then create a new SQL Server project (File > New > Project > Database Projects)
2. In the dialog box, select the database (in this example the NORTHWIND database) and click Yes to create the project.
3. Add a  new Stored procedure named SampleSproc to the project (this will also add a SampleSproc.cs file to the project.)
4. Enter some code into the stored procedure, for example:

   public partial class StoredProcedures
   {
   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void SimpleStoredProcedure(SqlString name)
   
   {
   using (SqlConnection sqlConn = new SqlConnection("context connection=true"))
   
   {
   String sqlStr = string.Format("Insert into [TableName] (FieldName, ...) values ('{0}', ...)", name);
   SqlCommand cmdObj = new SqlCommand(sqlStr, sqlConn);
   
   try
   {
   sqlConn.Open();
   cmdObj.ExecuteNonQuery();
   }
   
   catch (Exception ex)
   {
   }
   }
   }
   };

5. Enter a test script in the Test.sql file, eg:

   exec SimpleStoredProcedure 'testname1'
   
   select * from [TableName] where [FieldName] = 'testname1'

6. Right click the project,and then select to build and deploy it
7. Look in the Server Explorer to check and the stored procedure has been created
8. Right click the stored procedure and then select “Step into Stored Procedure” and Visual Studio will start the debugging.

For more info on SQL/CLR debugging: http://msdn.microsoft.com/en-us/library/ms165051(VS.80).aspx