SQL Server – Ability to create a join this way?

Consider the following tables:

Table: parts
part_id INT PK
VARCHAR part number

Table: Jobs
job_id INT PK
VARCHAR job_number

Table: Job_Parts
part_id INT FK PK
job_id INT FK PK
INT quantity

Execute the following declaration:

TO SELECT
p.part_number AS [Part],
j.job_number AS [Job],
jp.quantity AS [Quantity]
OF
dbo.parts AS p
LEFT EXTERNAL JOINT
dbo.job_parts AS jp
ON jp.part_id = p.part_id
Left external joint
dbo.jobs AS j
ON j.job_id = jp.job_id

Give a table that can give the following results:

Party | Work | Amount
Part1234 Job1 10
Part1234 Job2 5
Part1234 Job3 7

Suppose now that I have the above statement inside a function:

CREATE FUNCTION JobParts (
@PartNum VARCHAR = NULL,
@JobNum VARCHAR = NULL
) RETURN TABLE (
- SELECT THE DECLARATION
)

Is it possible for me to modify the table join in the select statement so that:

  1. Yes @PartNum or @JobNum have a value he joins where Article number or JobNumber equal to the value provided
  2. Yes @PartNum or @JobNum are NOit will only return the last record entered for a game.

To give an example, if I'm going to @ JobNum = & # 39; Job2 & # 39; I want the result that I am:

Party | Work | Amount
Part1234 Job2 5

Else if @JobNum is NO is by default just to show the last entry of the table, so my result would be:

Party | Work | Amount
Part1234 Job3 7