Lanka Developers Community

    Lanka Developers

    • Register
    • Login
    • Search
    • Categories
    • Recent
    • Tags
    • Popular
    • Users
    • Groups
    • Shop

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

    Back-End Development
    sql-server database
    2
    2
    826
    Loading More Posts
    • Oldest to Newest
    • Newest to Oldest
    • Most Votes
    Reply
    • Reply as topic
    Log in to reply
    This topic has been deleted. Only users with topic management privileges can see it.
    • akashmanujaya
      akashmanujaya last edited by

      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 Status=@status
      
      		Declare @FlightNumber char(5) =  'select Flight_number from @delay_info '
      		
      		Update Flight_shedule_date set Date=@date where Flight_number=@FlightNumber
      
      		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.

      1 Reply Last reply Reply Quote 2
      • nowferrifkan
        nowferrifkan last edited by

        Did you find the answer?

        1 Reply Last reply Reply Quote 0
        • 1 / 1
        • First post
          Last post

        1
        Online

        3.7k
        Users

        1.3k
        Topics

        5.3k
        Posts

        • Privacy
        • Terms & Conditions
        • Donate

        © Copyrights and All right reserved Lanka Developers Community

        Powered by Axis Technologies (PVT) Ltd

        Made with in Sri Lanka

        | |