如何为存储过程中的表字段声明空值 [英] How can i declare null value for table fields in stored procedure

问题描述

这是存储过程：

hi this is stored procedure:

DELIMITER $$

CREATE DEFINER=`gps`@`%` PROCEDURE `SpAssetLost`(in HistoryID int,in AssetLostID int,in HistoryName varchar(250),in Remarks varchar(250),
in UserID int,in InMode varchar(200))

Begin
    

	if inmode= 'INSERT' then		
		insert into trnassetlost(HistoryID,HistoryName,Remarks,UserID)Values(HistoryID,HistoryName,Remarks,UserID);		
	end if;
    if inmode='UPDATE' then
        update trnassetlost set HistoryID=HistoryID,HistoryName=HistoryName,Remarks=Remarks,UserID=UserID where
        AssetLostID=AssetLostID;
    end if;
    if inmode='DELETE' then
         update trnassetlost set IsActive='FALSE' where AssetLostID=AssetLostID;
    end if;
    if inmode='SELECT' then
        select * from trnassetlost;
    end if;

end

我使用c＃在我的类文件中编写了select函数：

I was wrote select function in my class file using c#:

 public Access objAccess = new Access();

 public DataSet SELECT(BALAssetLost ObjAl)
        {
            param = new MySqlParameter[1];
            param[0] = new MySqlParameter("@inmode", "SELECT");

            DataSet ds = objAccess.GetDataSet(param, "SpAssetLost");
            return ds;
        }

Access.cs:
 public DataSet GetDataSet(MySqlParameter[] param, string procedure)
        {
            try
            {
                establishConnection();
                try
                {
                    if (con.State == ConnectionState.Open)
                    {
                        con.Close();

                    }
                    con.Open();
                    MySqlCommand cmd = new MySqlCommand(procedure, con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddRange(param);
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return ds;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

现在我收到错误在集合中找不到参数''HistoryID'' .so我想在不需要的变量中设置null。我可以在mysql工作台中创建存储过程时声明为null。



请任何人帮助我。

请有人帮助我。

now i got error "Parameter ''HistoryID'' not found in the collection".so i want to set null in unwanted variables.how can i declare null while creating stored procedure in mysql workbench.

PLEASE ANYBODY HELP ME.
Please anybody help me.

推荐答案

创建 DEFINER =` gps` @`％` PROCEDURE `SpAssetLost`（ in HistoryID int ， AssetLostID int ， in HistoryName varchar （ 250 ）， 备注 varchar （ 250 ），
in UserI int ， InMode varchar （< span class =code-digit> 200 ））

开始


如果 inmode = ' INSERT' < span class =code-keyword>然后
插入 进入 trnassetlost（ HistoryID，HistoryName，Remarks，UserID）值（HistoryID，HistoryName，Remarks，UserID）;
end if ;
如果 inmode = ' UPDATE' 然后
update trnassetlost set HistoryID = HistoryID，HistoryName = HistoryName，备注=备注，UserID = UserID 其中
AssetLostID = AssetLostID;
end if ;
如果 inmode = ' DELETE' 然后
update trnassetlost set IsActive = ' FALSE' 其中 AssetLostID = AssetLostID;
end if ;
如果 inmode = ' SELECT' 然后
选择 * 来自 trnassetlost;
end if ;

end

CREATE DEFINER=`gps`@`%` PROCEDURE `SpAssetLost`(in HistoryID int,in AssetLostID int,in HistoryName varchar(250),in Remarks varchar(250), in UserID int,in InMode varchar(200)) Begin if inmode= 'INSERT' then insert into trnassetlost(HistoryID,HistoryName,Remarks,UserID)Values(HistoryID,HistoryName,Remarks,UserID); end if; if inmode='UPDATE' then update trnassetlost set HistoryID=HistoryID,HistoryName=HistoryName,Remarks=Remarks,UserID=UserID where AssetLostID=AssetLostID; end if; if inmode='DELETE' then update trnassetlost set IsActive='FALSE' where AssetLostID=AssetLostID; end if; if inmode='SELECT' then select * from trnassetlost; end if; end

我使用c＃在我的类文件中编写了select函数：

I was wrote select function in my class file using c#:

 public Access objAccess = new Access();

 public DataSet SELECT(BALAssetLost ObjAl)
        {
            param = new MySqlParameter[1];
            param[0] = new MySqlParameter("@inmode", "SELECT");

            DataSet ds = objAccess.GetDataSet(param, "SpAssetLost");
            return ds;
        }

Access.cs:
 public DataSet GetDataSet(MySqlParameter[] param, string procedure)
        {
            try
            {
                establishConnection();
                try
                {
                    if (con.State == ConnectionState.Open)
                    {
                        con.Close();

                    }
                    con.Open();
                    MySqlCommand cmd = new MySqlCommand(procedure, con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.AddRange(param);
                    MySqlDataAdapter da = new MySqlDataAdapter(cmd);
                    DataSet ds = new DataSet();
                    da.Fill(ds);
                    return ds;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

现在我收到错误在集合中找不到参数''HistoryID'' .so我想在不需要的变量中设置null。我可以在mysql工作台中创建存储过程时声明为null。



请任何人帮助我。

请有人帮助我。

now i got error "Parameter ''HistoryID'' not found in the collection".so i want to set null in unwanted variables.how can i declare null while creating stored procedure in mysql workbench.

PLEASE ANYBODY HELP ME.
Please anybody help me.

使用值 DBNull.Value [ ^ ]。

这篇关于如何为存储过程中的表字段声明空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！