SQL Server : run stored procedure in another stored procedure with WHILE

Question

I have an Office employees database with information about those employees, their JobName, their education (courses) and Courses needed to do their JOB right. Simple.

I wrote 2 SQL Server stored procedures which are running well.

• Stored procedures #1 takes in a Jobname and returns the name of people with this jobname
• Stored procedures #2 takes in a Name and returns the course (needed) names that haven't been completed by the employee

What I'm searching for is: running stored procedure #1 (using the @Jobname) to first get the name of an employee, pass this to stored procedure #2 (as @Name) and then returns the results - then take the next name from the result of procedure #1 and so on.

These are my procedures:

Procedure #1:

ALTER PROCEDURE [dbo].[Functie] 
    @JobName AS NVARCHAR(50)
AS
BEGIN
    SELECT JobName, Name
    FROM Employers
    WHERE JobName = @Functie
END

Procedure #2:

ALTER PROCEDURE [dbo].[ExceptTest] 
    (@Name AS NVARCHAR(50))
BEGIN
    SELECT coursename 
    FROM CourseNeeded
    WHERE CourseNeeded.coursename = @Name

    EXCEPT

    SELECT coursename 
    FROM CourseDone
    WHERE CourseDone.coursename = @Name
END

I tried this:

ALTER PROCEDURE [dbo].[Functie] 
    @JobName AS NVARCHAR(50)
BEGIN
    SELECT JobName, Name
    FROM Employers
    WHERE JobName = @Functie

    WHILE @JobName <> 0
    BEGIN
        EXEC Procedure2 @Name = Name
END

Answer 1

ALTER PROCEDURE [dbo].[Functie] 
@JobName AS NVARCHAR(50)
AS
BEGIN
declare @Name AS NVARCHAR(50) 
SELECT @Name = Name
FROM Employers
WHERE JobName = @JobName

SELECT coursename 
FROM CourseNeeded
WHERE CourseNeeded.coursename = @Name

EXCEPT

SELECT coursename 
FROM CourseDone
WHERE CourseDone.coursename = @Name
END

Answer 2

This should really be done as a 'set' rather than a loop. Imagine if you had a million people on here - it would need to do lots and lots of loops. Instead, the strength of databases is their ability to do all the functioning on all the rows at once.

As such, I've focussed on the set-based approach as an answer, and how to get from the "loop" approach to "set-based" approach. If you really want to do a WHILE loop, search Stack Overflow for 'WHILE LOOP SQL' or similar.

In terms of thinking and planning, there's nothing wrong to start with loops (indeed, for many people, thinking about the looping process is more natural). However, before coding it in SQL, try to convert the 'loop' into a 'set-based' answer. One way is to replace the approach "for each person, do this" to "for everyone, do this". Then you have to just work out how to get there.

You're half-way there with your answer - just a few more steps will get it done.

Below is an example of how you could approach this.

Also, as you haven't given us data structures etc - just some names - I made my own data structures for clarity. Also, for most of the answer, I have just reported Employee_ID, Job_ID etc rather than names - you can get those easily with referencing the tables though.

I've separated statements out for example purposes - but (as demonstrated at the end) you will be able to collapse/combine a lot of these into a single statement that gives you your answer.

PS I have assumed you meant 'employee' rather than 'employer' (i.e., you're talking about a person, not the company).

Data setup

-- Tables holding base data about employees, jobs and courses. 
-- Note that an employee can only have one job in this model.

CREATE TABLE #Employees (Employee_ID int PRIMARY KEY, Employee_Name nvarchar(100), Job_ID int)

CREATE TABLE #Jobs (Job_ID int PRIMARY KEY, Job_Name nvarchar(100))

CREATE TABLE #Courses (Course_ID int PRIMARY KEY, Course_Name nvarchar(100))


-- Tables holding links between these 
-- - the courses already completed by employees, and 
-- - the courses required for each job

CREATE TABLE #Employee_Course (Employee_ID int, Course_ID int, PRIMARY KEY(Employee_ID, Course_ID))

CREATE TABLE #Job_Course (Job_ID int, Course_ID int, PRIMARY KEY(Job_ID, Course_ID))

-- Note I haven't set up data or other things like Foreign Keys etc - to keep this as focussed as possible

Now, for processing.

Your approach was to first work out (for a given job) who the relevant employees were. From there, you checked each employee to see if they had all the courses needed.

We'll change that slightly - instead of checking each employee, we will check all employees at once.

-- Step 1 - Identify employees in job

    CREATE TABLE #Emps_in_job (Employee_ID int)
    
    INSERT INTO #Emps_in_job (Employee_ID)
        SELECT  E.Employee_ID
        FROM    #Employees E
        WHERE   E.Job_ID = @Job_ID
    
    SELECT * FROM #Emps_in_job ORDER BY Employee_ID

-- Step 2 - For all employees in that list, find the courses they need (based on their job)
-- Note this hasn't yet checked whether they have completed that course or not.

    CREATE TABLE #Emp_Course_Requirements (Employee_ID int, Course_ID int)

    INSERT INTO #Emp_Course_Requirements (Employee_ID, Course_ID)
        SELECT  E.Employee_ID, JC.Course_ID
        FROM    #Employees E
                INNER JOIN #Job_Course JC ON E.Job_ID = JC.Job_ID

    SELECT * FROM #Emp_Course_Requirements

-- Step 3 - Find out which courses are missing and report on them
-- There are three options here - all should work (select whichever you prefer)

    -- Option 1 (using EXCEPT clause - easy to read but not frequently used)
    SELECT      ECR.Employee_ID, ECR.Course_ID
        FROM    #Emp_Course_Requirements ECR
      EXCEPT
    SELECT      EC.Employee_ID, EC.Course_ID
        FROM    #Employee_Course EC

    -- Option 2 (using LEFT OUTER JOIN, and a NULL result in that joined table indicating they don't have it)
    SELECT      ECR.Employee_ID, ECR.Course_ID
        FROM    #Emp_Course_Requirements ECR
                LEFT OUTER JOIN #Employee_Course EC
                        ON ECR.Course_ID = EC.Course_ID
                        AND ECR.Employee_ID = EC.Employee_ID
        WHERE   EC.Employee_ID IS NULL

    -- Option 3 (Delete completed employee/courses from the list. Any remaining must still be required)
    DELETE FROM ECR
        FROM    #Emp_Course_Requirements ECR
                INNER JOIN #Employee_Course EC
                        ON ECR.Course_ID = EC.Course_ID
                        AND ECR.Employee_ID = EC.Employee_ID
    SELECT      * 
        FROM    #Emp_Course_Requirements ECR

However, even in the above there is extra processing. In the above we create temporary tables, fill them, and use them in the next step only. Instead, just put the original query into the second query (and so on) skipping the need to make a temporary table.

The final result could look like this

CREATE PROCEDURE #JobCourseCheck 
        @Job_ID int
AS
BEGIN
    SET NOCOUNT ON;
    
    SELECT      E.Employee_Name, C.Course_Name
        FROM    #Employees E
                INNER JOIN #Job_Course JC ON E.Job_ID = JC.Job_ID
                INNER JOIN #Courses C ON JC.Course_ID = C.Course_ID
                LEFT OUTER JOIN #Employee_Course EC
                        ON E.Employee_ID = EC.Employee_ID
                        AND JC.Course_ID = EC.Course_ID
        WHERE   E.Job_ID = @Job_ID
                AND EC.Employee_ID IS NULL

END