MySQl 存储过程传递带有参数名称和值的参数

Question

我正在使用存储过程。

create procedure uspCommonMasterInsertUpdateSingleItem
(
    p_id int,
    p_name varchar(50),
    p_head int,
    p_desc varchar(500),
    p_ct_nm varchar(50)
)
begin
    declare p_returnvalue int;
    declare p_ct_cd int ;
    set p_ct_cd = (select ct_cd from com_typ where ct_nm = p_ct_nm);

    if (p_id is null) then
        insert into com_mst(
            ct_cd,
            cm_nm,
            cm_hed,
            cm_dsg
        )
        values
        (
            p_ct_cd,
            p_name,
            p_head,
            p_desc
        ) ;
        select p_returnvalue = LAST_INSERT_ID();
    else
        update com_mst set
            ct_cd=p_ct_cd,
            cm_nm=p_name,
            cm_hed =p_head,
            cm_dsg = p_dsg 
        where cm_cd = p_id ;
        select p_returnvalue = p_id;
    end if ;
end

当我执行这个

时

call uspCommonMasterInsertUpdateSingleItem (p_name := 'kk',p_head := '1',p_desc := 'des',p_ct_nm := 'Department')

显示错误

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':= 'k
k',p_head := '1',p_desc := 'des',p_ct_nm := 'Department')' at line 1

Answer 1

只需更改调用方法即可

call uspCommonMasterInsertUpdateSingleItem (p_name := 'kk',p_head := '1',p_desc := 'des',p_ct_nm := 'Department')

至

call uspCommonMasterInsertUpdateSingleItem ('kk',1,'des','Department')

另外，请传递“p_id”的值作为上述调用方法的第一个参数