In my stored procedure insert into two different tables with if else condition-CodePudding

create procedure sp_student 
    @student varchar(50);
as 
    if (@student = marks)
    begin
        insert into marks 
        values (student_id int not null, terms varchar(10), subject1 varchar(100), subject2 varchar(100), total varchar(100))
    end
    else (@student = students)
    begin
        insert into students 
        values(student_id int not null, student_name varchar(100), student_age int, mobile_no varchar(20))
    end

I didn't get a answer above I mentioned query.

CodePudding user response：

you can't set dataType in value(..) :

create procedure sp_student 
    @student varchar(50),
    @student_id int,
    @terms varchar(10),
    @subject1 varchar(100),
    @subject2 varchar(100),
    @total varchar(100),
    @student_name varchar(100),
    @student_age int,
    @mobile_no varchar(20)
    
as 
    if (@student = 'marks')
    begin
        insert into marks 
        values (@student_id, @terms, @subject1 , @subject2 , @total)
    end
    else (@student = 'students')
    begin
        insert into students 
        values(@student_id ,@student_name, @student_age, @mobile_no)
    end

CodePudding user response：

Too many errors. Such a number of errors makes me feel, that you don't really use IBM Db2 product...

create procedure sp_student (
    @student varchar(50),
    @student_id int,
    @terms varchar(10),
    @subject1 varchar(100),
    @subject2 varchar(100),
    @total varchar(100),
    @student_name varchar(100),
    @student_age int,
    @mobile_no varchar(20)    
)
BEGIN
    if (@student = 'marks') then
        insert into marks 
        values (@student_id, @terms, @subject1 , @subject2 , @total);
    elseif (@student = 'students') then
        insert into students 
        values (@student_id ,@student_name, @student_age, @mobile_no);
    end if;
END

fiddle