I am using SQL and I have 4 tables.
tbl_document (Contains document_id as primary key and assignment_id as foreign key),
Now i need to write an stored procedure which will validate a project and returns the validation results, where project id will be passing as parameter.
I am having a temp table where I need to insert the validation errors like "Task - Task1 doesnt contains any assignment", "Assignemnt - Assignment2 doesnt contains any documnets " etc.
Is there any way to implement this logic without using Cursors
You don't need cursors for this type validation. Here are three queries that do this:
Projects with no tasks:
select p.*
from tbl_projects p left outer join
tbl_tasks t
on p.project_id = t.project_id
where t.project_id is null;
Tasks have at least one assignment:
select p.*
from tbl_tasks t left outer join
tbl_assignments a
on a.task_id = t.task_id
where a.task_id is null;
All assignments have at least one document:
select p.*
from tbl_assignments a left outer join
tbl_documents d
on a.assignment_id = d.assignment_id
where d.assignment_id is null;