How to update a table withing a User defined Function in SQL SERVER



  • Since I am new to sql server, I want to update Flight_shedule_date where the value of Flight_number witch is come from the @delay_info virtual table. Here what i have done. Please help me out

    CREATE FUNCTION delayInfo (@status varchar(50),@date date)
    
    RETURNS @delay_info TABLE 
    	(
    		Status varchar (50),
    		Remark varchar (50),
    		Arrival_teminal_number char (8),
    		Staff_ID char (5),
    		Leg_number char (10),
    		Flight_number char(5),
    		Passport_number char(10),
    		Passenger_name varchar(50),
    		Passenger_catogary varchar(30),
    		Passenger_Requirement varchar (50)
    		
    	)
    
    AS
    	BEGIN
    		
    		INSERT INTO @delay_info (Status,Remark,Arrival_teminal_number,Staff_ID,Leg_number,Flight_number,Passport_number,Passenger_name,Passenger_catogary,Passenger_Requirement)
    
    		SELECT Flight_leg_B.Status,Flight_leg_B.Remark,Flight_leg_B.Arrival_teminal_number,Flight_leg_A.Staff_ID,Flight_leg_A.Leg_number,Flight_leg_A.Flight_number,Passenger_A.Passport_number,Passenger_A.First_name+Passenger_A.Minit+Passenger_A.Last_name as Name, Passenger_catogary.Passenger_catogary,Passenger_requirements.Requirement
    		from Flight_leg_B 
    		JOIN  Flight_leg_A on (Flight_leg_B.Arrival_teminal_number=Flight_leg_A.Arrival_teminal_number)
    		JOIN  Aircrew on (Flight_leg_A.Staff_ID=Aircrew.Staff_ID)
    		JOIN Passenger_A on (Flight_leg_A.Leg_number=Passenger_A.Leg_number)
    		JOin Passenger_catogary on (Passenger_A.Passport_number=Passenger_catogary.Passport_number)
    		JOIN Passenger_requirements on (Passenger_A.Passport_number=Passenger_requirements.Passport_number)
    
    		Where [email protected]
    
    		Declare @FlightNumber char(5) =  'select Flight_number from @delay_info '
    		
    		Update Flight_shedule_date set [email protected] where [email protected]
    
    		Print 'Flight Shedule date Updated'
    		
    			
    
    		RETURN
    		END
    
    

    When i execute the query it will show this error:
    Msg 443, Level 16, State 15, Procedure delayInfo, Line 35 [Batch Start Line 0]
    Invalid use of a side-effecting operator 'UPDATE' within a function.
    Msg 443, Level 16, State 14, Procedure delayInfo, Line 37 [Batch Start Line 0]
    Invalid use of a side-effecting operator 'PRINT' within a function.



  • Did you find the answer?


Log in to reply