User defined function and INSERT

• Xavier Cabezas

  SSC Rookie

  Points: 46

  More actions

  December 14, 2005 at 12:26 pm

  #188675

  When I try the following:

  CREATE FUNCTION udf_GetEvaluationIdByProjectId(@project_id int) 

  RETURNS int

  AS 

  BEGIN

   Declare @count int

   Declare @id int

   

   Select @count = Count(id) FROM tva_evaluation WHERE (project_id = @project_id)

   

   /*

   IF @count = 0

   

    BEGIN

   

     INSERT INTO tva_evaluation

     (project_id,tier_1,tier_2)

     

     VALUES

     (@project_id,0,0)

   

   

    END

   */

   

   SELECT @id = id

   FROM tva_evaluation

   WHERE (project_id = @project_id)

   Return @id

  END

  I get an error on the code between /* ... */ saying that I cannot use INSERT on a function.   Any ideas?

  Thanks in advance for your help.

  XC

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  December 14, 2005 at 2:07 pm

  #609582

  Function MUST NOT modify anything in database.

  It just to display results calculated from current state and/or supplied values.

  What you are trying to achieve must be done in SP or trigger.

  _____________
  Code for TallyGenerator