CODEWITHSUNDEEP
sqlsql-server-2008
Nick Kahn
Nick Kahn

Reputation: 20078

How to troubleshoot a stored procedure?

what is the best way of troubleshoot a stored procedure in SQL Server, i mean from where do you start etc..?

Upvotes: 1

Views: 10192

Answers (4)

Mark SQLDev
Mark SQLDev

Reputation: 539

In SSMS, you can simply start by opening the proc., and clicking on the check mark button (Parse) next to the Execute button on the menu bar. It reports any errors it finds.

If there are no errors there and you're stored procedure is harmless to run (you're not inserting into tables, just creating a temp table for example), then comment out the CREATE PROCEDURE x (or ALTER PROCEDURE x) and declare all the parameters by copying that part, then define them with valid values. Then run it to see what happens.

Maybe this is simple, but it's a place to start.

Upvotes: 0

HLGEM
HLGEM

Reputation: 96572

Troubleshooting a complex stored proc is far more than just determining if you can get it to run or not and finding the step which won't run. What is most critical is whether it actually returns the corect results or performs the correct actions.

There are two kinds of stored procs that need extensive abilites to troublshoot. First the the proc which creates dynamic SQL. I never create one of these without an input parameter of @debug. When this parameter is set, I have the proc print the SQl statment as it would have run and not run it. Almost everytime, this leads you right away to the problem as you can then see the syntax error in the generated SQL code. You also can run this sql code to see if it is returning the records you expect.

Now with complex procs that have many steps that affect data, I always use an @test input parameter. There are two things I do with the @test parameter, first I make it rollback the actions so that a mistake in development won't mess up the data. Second, I have it display the data before it rollsback to see what the results would have been. (These actually appear in the reverse order in the proc; I just think of them in this order.)

Now I can see what would have gone into the table or been deleted from the tables without affecting the data permananently. Sometimes, I might start with a select of the data as it was before any actions and then compare it to a select run afterwards.

Finally, I often want to log actions of a complex proc and see exactly what steps happened. I don't want those logs to get rolled back if the proc hits an error, so I set up a table variable for the logging information I want at the start of the proc. After each step (or after an error depending on what I want to log), I insert to this table variable. After the rollback or commit statement, I select the results of the table variable or use those results to log to a permanent logging table. This can be especially nice if you are using dynamic SQL because you can log the SQL that was run and then when something strange fails on prod, you have a record of which statement was run when it failed. You do this in a table variable because those do not go out of scope in a rollback.

Upvotes: 1

user2043
user2043

Reputation:

Visual Studio 2008 / 2010 has a debug facility. Simply connect to to your SQL Server instance in 'Server Explorer' and browse to your stored procedure.

Visual Studio 'Test Edition' also can generate Unit Tests around your stored procedures.

Upvotes: 2

Will Marcouiller
Will Marcouiller

Reputation: 24132

  1. Test each SELECT statements (if any) outside of your stored procedure to see whether it returns the expected results;
  2. Make INSERT and UPDATE statements as simple as possible;
  3. Try to test Inserts and Updates outside of your SP so that you can check it gives the expected results;
  4. Use the debugger provided with SSMS Express 2008.

Upvotes: 2

Related Questions