T-SQL, Cursors, Best Practices of pulling from multiple tables

[Arielle]

Good afternoon, here I am again with a new bugger of a problem. I'm working on an update for an existing program that uses classic ASP with a good SQL Server backend. The application server is kinda dink and my task lead wants most of the data to be handled on the database end of things. My problem? While I can write the queries of doom I'm kinda new to T-SQL.

Here's the issue. I've got projects, tasks, departments, point of contacts, and managers. I'm working on a report that for each project selected by (we'll say the site manager) to display for every project selected a list of departments associated with that project and their task status, the point of contact for that department, their 2nd tier supervisory department, the 2nd tier point of contact, and the projects due date/information, and overall status.

What I need is for each project I pass in to loop through the departments listed associated with that project and based on the COC pull their 2nd tier (already got a query for this from another thread), loop through the tasks associated with each project/department as they are related, then from a completely unrelated source pull in the POC's as associated with the department.

From what I've gathered talking to my collegues the best course of action is most likely setting up a temporary table within a stored procedure and using a cursor. My question is, can you nest cursors and how so?

-------
Basically, I have severe doubt anyone here could help me. Any book recommendations that talks about cursors and nesting? Thanks.

[iburyak]

See example from T-SQL help.

[PHP] Use nested cursors to produce report output
This example shows how cursors can be nested to produce complex reports. The inner cursor is declared for each author.

SET NOCOUNT ON

DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
@message varchar(80), @title varchar(80)

PRINT "-------- Utah Authors report --------"

DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = "UT"
ORDER BY au_id

OPEN authors_cursor

FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT " "
SELECT @message = "----- Books by Author: " +
@au_fname + " " + @au_lname

PRINT @message

-- Declare an inner cursor based
-- on au_id from the outer cursor.

DECLARE titles_cursor CURSOR FOR
SELECT t.title
FROM titleauthor ta, titles t
WHERE ta.title_id = t.title_id AND
ta.au_id = @au_id -- Variable value from the outer cursor

OPEN titles_cursor
FETCH NEXT FROM titles_cursor INTO @title

IF @@FETCH_STATUS <> 0
PRINT " <<No Books>>"

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @message = " " + @title
PRINT @message
FETCH NEXT FROM titles_cursor INTO @title

END

CLOSE titles_cursor
DEALLOCATE titles_cursor

-- Get the next author.
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
END

CLOSE authors_cursor
DEALLOCATE authors_cursor
GO[/PHP]

[Arielle]

Thank you very much.

I've managed to make the stored procedure neccessary. Tre fabulous! Now that my brain has been sufficiently picked for a few days...

[iburyak]

I remember you had difficult case where data has comma separated values.

I hate cursors and would not recommend them to anyone but in your case it is easier for you I guess.... :)

Good job.

Good luck.