Debugging SQL Server 2005 Stored Procedures

Can you clarify, is this an Access database or an SQL Server database? They are very different...

SQL 2005 back end, function and view all in the back end, just use access as front end for reporting.

Well one way of debugging, is to run the query in SQL Query Analyser... Thats certainly how I build and debug my queries. Then once you have the database side of things working you can debug the front end as you normally would.

Access front-end is OK..  IMHO, if you have ability to run SSMS, (SQL Server Management Studio) - you will want to use that.  It is much more powerful than Access.  One of the things Access does is really why I went away from it is the fact that when you query even one row in a multi-row table, it will pull all of the rows across.  It is not a very good database in an Enterprise environment.  The other thing that is frustrating is at my previous job, we had a lot of little Access databases, where everybody had their own thoughts on database design and as a consequence it was terrible!  Access is great for reporting, but you should really look into the use SSRS (SQL Server Reporting Services).  Funny thing, I don't recall the last time I have used MS Access.

hi, thanks for replying.
@ dale_burrell: as I am not so familiar with all sql tools and how they are being called, please excuse me if I am asking something too simple or perhaps wrong..
we have SSMS and for creating simple queries/views that's fine, however the situation here is as follows, we have a view that contains a custom function stored in sql, that includes some logic involving variables etc...now for some reason the results is not returning as expected...therefore I am looking for a way to run it step by step, and see where exactly the problem is laying, the way I would perform in Access...
@kdyer: you're correct, and that is the way we have our setup, BE Sql and FE MS Access.

Query Analyser is one of the tools in SSMS, you can do far more than simple queries in it :)

Run the query that is returning incorrect results. Have the function logic in a second window and run it using the results from a single incorrect row from the view and work from there.

You can add as many intermediate 'selects' as you like to see what is happening to the data along the way and where it is going wrong.

ok, let me give you some more details here,
the function is being called with about 30 parameters, and some logic is programmed to assign finally a string to the returning variable string, somewhere down the road it loses the correct value, not necessary by a select statement, so I'm not sure how can this be accomplished by the query analyzer.

All in a days work mate :) you copy the T-SQL from the function into an empty query window, assign the variables for a case that fails, then add a combination of select and print statements at various points in the logic until you narrow down where its going wrong.

However if thats not appealing it appears that Query Analyser has a built in debugger http://www.techrepublic.com/article/debugging-stored-procedures-in-sql-servers-query-analyzer/

hi, just tried the suggestion from the link you supplied and it says there:

3.Select the stored procedure of interest.
4.Right-click on the stored procedure's name and select Debug from the pop-up menu

however its not displaying by me the debug menu in as part of the choices.
Is it possible that this does not come with SQL Express version? or maybe there is something else I'm missing here?

Hi dale_burrell,
re your first suggestion, given the small example (see attached), would you be able to guide me how to apply it there?
10-9-13.txt

Hi dale_burrell,
The code was just a sample of what I am having to debug, in actuality the @employeeid is being used... however your second point about original value was the culprit (as I am used to VBA variables where this works without assigning anything).
Thanks a lot for your example of debugging, will be handy if I ever need it in the future..