Visual Studio can debug TSQL stored procedures, functions, and triggers in SQL Server 7.0, 2000 and SQL Server 2005.
In SQL Server 2005, CLR integration allows you to write stored procedures, triggers, user-defined types, and user-defined functions using any .NET Framework language, including Microsoft Visual Basic .NET and Microsoft Visual C#.
I assume you have already created SQLCLR stored procedure, function or trigger object.
Note: You will require Visual Studio 2005 and SQL Server 2005 (or SQL Server 2005 Express) to develop SQLCLR objects.
SQLCLR Stored Procedure:
Let say you have created a SQLCLR Stored Procedure.
Open existing SQL Server Project with SQLCLR Stored Procedure in Visual Studio 2005.
Right click the SQL Server Project in Solution Explorer and click Deploy.
Click View menu and select Server Explorer. In Server Explorer, add Database Connection if it does not exist. This should be same database connection, which you choose when creating SQLCLR object.
There is no need to create the assembly in SQL Server 2005, if you just want to debug the stored procedure or function in Visual Studio 2005.
Now go to Assemblies folder in database connection in Server Explorer. You would find the assembly with your project name. Inside that, you would find the object with your stored procedure name.
If you have added the following line before stored procedure, when you created the Stored Procedure in Visual Studio 2005, then Stored Procedure will appear with that name in Assemblies folder in Server Explorer.
[SqlProcedure(Name = "<Stored Procedure Name>")]
Image: Selecting SQLCLR Stored Procedure in Server Explorer
Right click the Stored Procedure in Server Explorer and click Open to open the stored procedure in Visual Studio 2005.
Right click the Stored Procedure in Server Explorer and click Execute to execute the stored procedure from Visual Studio. After execution, you would find the results in Output window.
In Output Window, Select 'Database Output' or 'Debug' from 'Show output from' dropdown to view the output results of stored procedure.
Right click the Stored Procedure in Server Explorer and click Step Into Stored Procedure. Debuuger will stop at first statement in Stored Procedure. Now you will get full debugger support in Visual Studio to debug the Stored Procedure. You can execute stored procedure one step at a time using F10 or F11 key. You can view different output windows like Autos, Local, Immediate, Call Stack etc.
SQLCLR User-Defined Function:
Similarly you can create the SQLCLR user-defined function, deploy the SQL Server project and debug that user-defind function from Assemblies folder in Server Explorer.