Debug and Execute the LookupEMP Stored Procedure

Configure the debugger

1. In Visual Studio, start the SQL Server Object Explorer.
2. On the SQL Server Object Explorer, click Add SQL Server .
3. On the History tab under Recent Connections, select SQLCLR_Test; then click Connect.
   Note: If Microsoft SQL Server prompts you with an Attach Security Warning, please click Attach to clear the prompt.
4. On the SQL Server Object Explorer, right-click your SQL Server instance, represented by a dot followed by your local server information in parentheses, and check Application Debugging and Allow SQL/CLR Debugging.
   Note: If a prompt appears, click Yes to enable SQL CLR debugging.

Run the SQL/CLR debugger

1. On the SQL Server Object Explorer, expand the entry for your local SQL Server.
2. Expand Databases > SQLCLR_Test > Programmability > Stored Procedures.
3. When the SQL Server Object Explorer has refreshed, right-click the dbo.LookupEMP stored procedure and select Debug Procedure.
4. In the Value field for the @lkEMPNO name, type 000020.
5. Check the box in the Null column for the @lkFIRSTNME and @lkLASTNAME names, and then click OK.

   Visual Studio connects to your SQL Server instance, creates a test SQL script, calls your stored procedure, and runs the debugger with your cursor on the USE [SQLCLR_Test] statement of the test SQL script.

6. Use the Visual Studio debugger to step through the lines of code in your stored procedure, examining variables and values, etc.

   The Data Tools window on the Results tab displays information returned from the debugger.

7. To exit the debugger, click Debug > Stop Debugging.

Execute the LookupEMP stored procedure

You can execute the stored procedure from the SQL Server Object Explorer. By providing required input values, you can see the output that results from the stored procedure call.

1. From the SQL Server Object Explorer, right-click the dbo.LookupEMP stored procedure, and select Execute Procedure.
2. If not already set, type 000020 into the Value field for the @lkEMPNO name, and check Null for the other two names.
3. Click OK to execute the stored procedure.
4. View the results on the Results tab:
   
   
5. Close the SQL Server Object Explorer.