SQL Server Stored Procedures – How to Return Multiple Tables

sql serverstored-procedures

I apologize if the title for the question doesn't match what I am asking what to do. I am not sure how to describe what I am trying to accomplish.

I have the following tables in my database:

Table customers
    customer_id (PK, int, NN)
    customer_name (varchar(100), NN)

Table jobs
    job_id (PK, int, NN)
    job_number (varchar(10), NN)
    job_description (varchar(MAX), NN)
    customer_id (FK, int, NN)

Table job_statuses
    status_id (PK, int, NN)
    status_description (varchar(50), NN)

Table job_status_logs
    log_id (PK, int, NN)
    job_id (FK, int, NN)
    status_id (FK, int, NN)
    log_date (date, NN)

Now I am trying to program a procedure where I pass it a Job Number and it does the following:

1) Checks if the Job Number exists within the database, if not, return an error message.

2) If the Job Number exists return the following columns from the following tables:

jobs: job_id, job_number, job_description
customers: customer_name
job_status_logs: log_date (WHERE status_description is 'Open') & log_date (WHERE status_description is 'Closed')

I am mainly having an issue with the very last part, as I am unsure how to form the query looking for the last two points of data (the ones from the job_status_logs table).

EDIT: This is currently what I have for the procedure

CREATE PROCEDURE dbo.GetJobData(
    @JobNum   VARCHAR(10),
    @response NVARCHAR(500) OUTPUT
) AS
BEGIN
    DECLARE @JobID INT
    SELECT @JobID = job_id
      FROM jobs
     WHERE job_number = @JobNum
    IF @JobID IS NULL
    BEGIN
        SET @response = 'Cannot find data for Job "' + @JobNum + '".  Job does not exist.'
        RETURN
    END
    SELECT j.job_id AS [ID], j.job_number AS [Job], j.job_description AS [Description], c.customer_name AS [Customer]
      FROM jobs AS j INNER JOIN
           customer AS c ON c.customer_id = j.customer_id
     WHERE j.job_id = @JobID
END
GO

As stated above, I do not know how to set up the query to grab the data I am looking for from the job_status_logs table.

Best Answer

Try this - there are two examples. The first result (jobid=100) has both and open and a closed logdate. The second result (jobid=200) only has an open logdate. Take note of the LEFT JOIN against the 'closed' statuses.

--demo setup
DECLARE @customers TABLE (
    customer_id INT
    ,customer_name VARCHAR(100)
    )
insert into @customers(customer_id,customer_name) values(1,'bob')

DECLARE @jobs TABLE (
    job_id INT
    ,job_number VARCHAR(10)
    ,job_description VARCHAR(MAX)
    ,customer_id INT
    )
insert into @jobs(job_id,job_number,job_description,customer_id) values
(1,'100','jobdesc',1),(2,'200','jobdesc2',1)

DECLARE @job_statuses TABLE (
    status_id INT
    ,status_description VARCHAR(50)
    )
insert into @job_statuses(status_id,status_description) values(1,'open'),(2,'closed')

DECLARE @job_status_logs TABLE (
    log_id INT
    ,job_id INT
    ,status_id INT
    ,log_date DATE
    )
insert into @job_status_logs(log_id, job_id,status_id,log_date) values
(1,1,1,'2018-10-01'),(2,1,2,'2018-11-01'),(3,2,1,'2018-12-01')

--the solution
--parameter passed into SP
DECLARE @JobNumber INT

SET @JobNumber = 100    --has both open and closed status

SELECT j.job_id
    ,j.job_number
    ,j.job_description
    ,c.customer_name
    ,jslo.log_date AS LogOpen
    ,jslc.log_date AS LogClosed
FROM @jobs j
JOIN @customers c
    ON c.customer_id = j.customer_id
JOIN (
    SELECT jsl.job_id
        ,log_date
    FROM @job_status_logs jsl
    JOIN @job_statuses js
        ON js.status_id = jsl.status_id
            AND js.status_description = 'open'
    ) jslo
    ON jslo.job_id = j.job_id
LEFT JOIN (
    SELECT jsl.job_id
        ,log_date
    FROM @job_status_logs jsl
    JOIN @job_statuses js
        ON js.status_id = jsl.status_id
            AND js.status_description = 'closed'
    ) jslc
    ON jslc.job_id = j.job_id
WHERE j.job_number = @JobNumber

---------------------------

SET @JobNumber = 200    --has only open status

SELECT j.job_id
    ,j.job_number
    ,j.job_description
    ,c.customer_name
    ,jslo.log_date AS LogOpen
    ,jslc.log_date AS LogClosed
FROM @jobs j
JOIN @customers c
    ON c.customer_id = j.customer_id
JOIN (
    SELECT jsl.job_id
        ,log_date
    FROM @job_status_logs jsl
    JOIN @job_statuses js
        ON js.status_id = jsl.status_id
            AND js.status_description = 'open'
    ) jslo
    ON jslo.job_id = j.job_id
LEFT JOIN (
    SELECT jsl.job_id
        ,log_date
    FROM @job_status_logs jsl
    JOIN @job_statuses js
        ON js.status_id = jsl.status_id
            AND js.status_description = 'closed'
    ) jslc
    ON jslc.job_id = j.job_id
WHERE j.job_number = @JobNumber

Result1

| job_id | job_number | job_description | customer_name | LogOpen    | LogClosed  |
|--------|------------|-----------------|---------------|------------|------------|
| 1      | 100        | jobdesc         | bob           | 2018-10-01 | 2018-11-01 |

Result2

| job_id | job_number | job_description | customer_name | LogOpen    | LogClosed |
|--------|------------|-----------------|---------------|------------|-----------|
| 2      | 200        | jobdesc2        | bob           | 2018-12-01 | NULL      |
Related Question