How can I check if the ID’s of a table match another ID’s of another table in SQL

So I have 4 parameters, one of them its given as an input parameter of a stored procedure (its like a code that represents an unique action), and I need to match these ID’s with another ID’s in another table, so I need to check in table 2 the ID’s “DeliverTypeID”, “ScheduleTypeID”, “CourseID” if they are all the same as table 1, if they really are then it should print me out the corresponding records.

I need to do this selection by a Stored Procedure.

Here’s my code so far:

USE [Database]
GO
/****** Object:  StoredProcedure [dbo].[nameSP]  ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[nameSP]  
    @ScheduleID int
AS
BEGIN   
    SET NOCOUNT ON;

    DECLARE @DeliverTypeID int
    DECLARE @ScheduleTypeID int
    DECLARE @CourseID int

    SELECT @DeliverTypeID = DeliverTypeid
        , @ScheduleTypeID = [Cod Tipo Acção]
        , @CourseID = [Cod Curso]
    FROM table1
    INNER JOIN table2 on table1.[CourseID] = table2.CourseID
    INNER JOIN table2 on table1.[ScheduleTypeID] = table2.ScheduleTypeID
    INNER JOIN table2 on table1.[DeliverTypeID] = table2.DeliverTypeID
    WHERE table1.[Cod Acção] = @ScheduleID
    AND (CourseID = @CourseID OR table2.CourseID is null)
    AND (ScheduleTypeID = @ScheduleTypeID)
    AND (DeliverTypeID = @DeliverTypeID)
END

Minimal Example :

Table1

ScheduleID|CourseID|DeliverTypeID|ScheduleTypeID|
67212     |2000    |1            |1             |
69212     |3000    |2            |2             |
69124     |4000    |3            |3             |
70124     |5000    |4            |4             |

Table2
|CourseID|DeliverTypeID|ScheduleTypeID|
|3000    |1            |1             |
|2000    |2            |3             |
|5000    |3            |2             |
|NULL    |2            |1             |

Thank you very much in advance.

Answer

I think what you are looking for is the following. I don’t think you need the variables, just the correct logic in your single join (it seems unlikely you want to join Table2 on 3 times).

ALTER PROCEDURE [dbo].[nameSP]
(
    @ScheduleID int
)
AS
BEGIN   
    SET NOCOUNT, XACT_ABORT ON;

    -- Select specific columns you wish to return rather than *
    SELECT t2.*
    FROM table1 t1
    INNER JOIN table2 t2 ON (t2.CourseID = t1.[CourseID] OR t2.CourseID IS NULL)
        AND t2.[ScheduleTypeID] = t1.ScheduleTypeID
        AND t2.[DeliveryTypeID] = t1.DeliveryTypeID
    WHERE t1.ScheduleID = @ScheduleID;

    -- Tell the calling context that the SP executed OK (0 means OK any other number means error).
    RETURN 0;
END;

Note I recommend the use of table aliases for clarity.