Why did I get this error in SQL Server ? How can I fix this? [closed]

by

I want to write a procedure that takes student, semester, type of payment and amount as input check if that student had taken a course in that semester, pay one row in the table Add it with the description of the discount and if the student was not registered in that semester, pay the amount in half His previous ones should be divided equally.

create proc sp_AddPay(@id int , @term int , @PayType int , @Value money)  as  if    (
        exists(
               select distinct 1
               from EDU.Student s
               join EDU.Selection sct on s.Id = sct.StudentId
               join EDU.Unit u on sct.UnitId = u.Id
               where s.Id = @id and u.TermId = @term
              )
        )  insert into ML.Pay ([StudentId], [TermId], [PayTypeId], [Value] , [Description])

 values(@id , @term , @PayType , @Value , 'discount' )  else(
        with cte as(
                      select  distinct @Value as Pay,
                                                (
                                                 select count(*) PCount
                                                 from ML.pay p
                                                 where p.StudentId = @id
                                                ) PayConut
                      from Ml.Pay p
                      where p.StudentId = @id
                      )
        select *
        from cte
        outer apply(
        update  Ml.Pay
        set [Value] = [Value] + (cte.Pay / cte.PayConut)
                   )

  • That’s all kinds of wrong… You can’t dump an UPDATE inside a subquery/derived table, your dataset from the APPLY has no alias, and SELECT \* isn’t valid syntax. You need to explain what you’re trying to do here, not just dump some malformed code.

    – 

  • Also please indicate which line causes the error (it’s valid for all questions with an error message). As it is a part of your code, we can’t know which one is line 917. To add to the problems noted by Thom A, I think there should be a as in here: ) as PayConut

    – 

  • you mean if student was in a course, then pay for the course, otherwise add this payment to his all other payments equally?

    – 

Your syntax is a bit strange, but it’s pretty close:

CREATE PROCEDURE sp_AddPay(@id int , @term int , @PayType int , @Value money)
AS
BEGIN
 
    IF EXISTS(
            SELECT  1
            FROM    EDU.Student s
            INNER JOIN EDU.Selection sct
                ON  sct.StudentId = s.Id
            INNER JOIN EDU.Unit u
                ON  u.Id = sct.UnitId
            WHERE   s.Id = @id
            AND u.TermId = @term
        )
    INSERT INTO ML.Pay ([StudentId], [TermId], [PayTypeId], [Value] , [Description])
    VALUES(@id , @term , @PayType , @Value , 'discount')
     
    ELSE
    BEGIN

        UPDATE  p
        SET Value = Value + @Value / (
                                         SELECT count(*)
                                         FROM   ML.pay p2
                                         WHERE  p2.StudentId = p.StudentId
                                        )
        FROM    Ml.Pay p
        WHERE   p.StudentId = @id
    END
END

You don’t need a CTE in this case, so i removed it, as well as cleaned up some other issues

Leave a Comment