CODEWITHSUNDEEP
c#asp.netsql-server-2008
Romay_0987
Romay_0987

Reputation: 85

Compare multi rows with a row in SQL Server 2008

I have an asp.net C# project, for study case, "A University Website"

I have a table called Agenda and it contain 9 columns

[AgendaId]   uniqueidentifier
[MatirelId]
[InstructorId]
[ClassId]
[AddedDate]
[Semester]
[StartTime]
[EndTime]
[DaySchedule]

and it shows what the material is available, which instructor, in which days, in which time will start and will end, in which class, in the same semester.

What I need: when I add a new row, I should check if it is a overlapping of an existing row. If it is have an overlapping , give me an error.

PS: instructorId, MatirelId and ClassId are foreign keys into other tables.

Prefer to make it in a stored procedure in SQL Server 2008.

Kindly & Best Regards

Upvotes: 1

Views: 299

Answers (1)

marc_s
marc_s

Reputation: 754268

Assuming that all columns have to match for a duplicate - then you'd use something like this:

CREATE PROCEDURE dbo.InsertNewRow 
    @AgendaId      uniqueidentifier.,
    @MatirelId     int,   -- you did not mention what *types* those columns have - adapt as needed!
    @InstructorId  int,
    @ClassId       int,
    @AddedDate     datetime,
    @Semester      int
    @StartTime     time(7),
    @EndTime       time(7),
    @DaySchedule   int
AS BEGIN
   IF EXISTS (SELECT * FROM dbo.Agenda 
              WHERE MatirelId = @MatirelId
                AND InstructorId = @InstructorId
                AND ClassId = @ClassId
                AND Semester = @Semester
                AND StartTime = @StartTime
                AND EndTime = @EndTime
                AND DaySchedule = @DaySchedule)
        RAISERROR .......
        RETURN

   INSERT INTO dbo.Agenda(AgendaId, MatirelId, InstructorId, ClassId, 
                          AddedDate, Semester, StartTime, EndTime, DaySchedule)
   VALUES(@AgendaId, @MatirelId, @InstructorId, @ClassId, 
          @AddedDate, @Semester, @StartTime, @EndTime, @DaySchedule)
END

Read the excellent MSDN documentation to learn about how exactly to call RAISERROR to cause an error to be thrown from your stored procedure!

Upvotes: 2

Related Questions