CODEWITHSUNDEEP
sqlstored-proceduressql-server-2012
Ammar Ahmed
Ammar Ahmed

Reputation: 15

Facing Duplication Error In SQL Server stored procedure

I'm working on my final year project which is a School management system, there are many tasks in this project but i'm working on attendance module at this time, I have created a sql server table which contains four columns :

  1. Student Name
  2. Class Name
  3. Attendance Status (Column name is "Status")
  4. Date (Current Date for attendance)

and then I created a stored procedure. This stored procedure will get student name, class name and attendance status as parameter and then adds this to attendance table which shows if student is present on specified date or absent, the date column have default value of getDate().

The stored procedure first checks if the student attendance is already present in the table for current date, then it changes the attendance status column value rather than adding a new row, and if the student attendance is not present for current date then stored procedure will add new row,

BUT......

My procedure is not working as expected, when I first pass parameters, it adds the new row, when I again pass the same parameters, then it should replace attendance status column but it again adds a new row.

But the third time, when I pass same parameter, then it works correctly and replaces status column value, so in this case I have 2 rows for the same student but I need only one row.

Please guide me what is the problem in my stored procedure -- here is the code:

create procedure spAttend_Stu
    @Name nvarchar(200),
    @C_Name nvarchar(50),
    @Status nvarchar(50)
as
begin
    if ((select count(*) 
         from tbl_Attend_Stu 
         where Student_Name = @Name 
           and Date = (select convert(date, getdate()))) > 1)
       update tbl_Attend_Stu 
       set [Status] = @Status 
       where [Date] = (select convert(date, getdate()))
    else
       insert into tbl_Attend_Stu (Student_Name, Class_Name, Status)
       values (@Name, @C_Name, @Status)
end

EDIT :

Here is my table definition..see this screenshot:

Table structure

and I'm passing this value as an example 

spAttend_Stu 'Ammar', 'BSCS', 'Absent'

I'm running the same query again, but result is not as expected

view this screenshot for example execution

example execution

Upvotes: 0

Views: 258

Answers (1)

sadmicrowave
sadmicrowave

Reputation: 40892

It would be helpful if you posted the schema constructor syntax for your tbl_Attend_Stu so we could see the field Date and its default value. However, there are a couple of initial thoughts for you to check (Note: without seeing examples of what is being passed in, and what values are in your table it is hard to know for sure) :

  1. Perhaps the value of @Name is not what you expect it to be when it is getting passed into your SP. If the value varies, even slightly, then the first IF statement logic won't find the student and another insert will happen. One potential fix could be to ensure the @Name and @C_Name variables are trimmed properly; if they are being passed into the SP with spaces, then it might be your culprit.
  2. If you default value configuration for Date is not setup properly then no date will get inserted when the INSERT statement is called. Try explicitly setting Date within the INSERT statement to CONVERT(DATE, GETDATE()) and see if this gives you what you expect.
  3. I found other issues with spots in your code. For example, you're missing a second condition in your UPDATE statement logic to only update the row for the specific student. The way you have it now will update the Date field for every row in the table.

I rewrote your logic here to make it a bit more readable, and fix the error I mentioned above, and small other syntax typos:

create procedure spAttend_Stu
@Name nvarchar(200),
@C_Name nvarchar(50),
@Status nvarchar(50)
AS
BEGIN

IF
( 
    (SELECT COUNT(*) 
     FROM   tbl_Attend_Stu 
     WHERE  Student_Name = @Name 
        AND [Date] = CONVERT(DATE, GETDATE()) 
    )
    >= 1
)
    UPDATE tbl_Attend_Stu 
    SET [Status] = @Status 
    WHERE   Student_Name = @Name
        AND [Date] = CONVERT(DATE, GETDATE())

ELSE

    INSERT INTO tbl_Attend_Stu
        (Student_Name, Class_Name, Status)
    VALUES
        (RTRIM(LTRIM(@Name)), RTRIM(LTRIM(@C_Name)), @Status)

END

Upvotes: 1

Related Questions