Replacecomplex SQL statement so it's easier to trace

[sobeit]

I have many stored procedures with SQL statments that have
many joins and 'where' clauses which make it very hard to
figure out while certain data are being duplicated in the report. It's very time consuming trying to figure out
whether it's a data problem or a logic problem in the
statement. I am thinking that it will be a lot easier to
track down the problem if i break out the SQL into many
stored procedures and I debating whether to write the
between result sets into a cursor or a temp table.
Is this a better solution than leaving the SQL in one SP??
Is a cursor better than a table? include a view instead?

Thanks.

 

[tlbroadbent]

If the stored proc has logical modules that could become SP, you may be wise to create separate SP's controlled by one "master" SP.

Proper use of Temporary tables is usually more efficient than using cursors. Of course, this depends on the application. Temp tables are more efficient because SQL Server is optimized to deal with relations (tables). Cursors provide ability to process records one-by-one. SQL Server handles this step-by-step process much less efficiently than queries that act on sets of data.

Of course, it also handles simple queries better than complex queries. Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[ColinM]

General advice is to try and stick with set processing (tables) rather than rows (cursors) as its more efficient.
While it may be hard to read the statement maybe optimized for performance.

I would advise against breaking the sql statement into separate sections if speed is a major issue.

As for one or many stored procedures, I don't really think there would be much difference, just your personal preference.

I understand that it is harder to read as I have recently do the opposite and converted a cursor procedure into one large (26 joins!) insert statment. While harder to maintain the new procedure takes 2 mins instead of 9 hours B-)