多个查询是单个存储过程。

Question

我试图使用单个存储过程来插入、更新、删除和选择语句。单独地，它们可以很好地工作，但是合并后，我在上面的所有查询中都会得到以下错误。

过程或函数需要未提供的参数。

这是手术。

USE [XXX]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[spCmpStructure]

    @action varchar(7),
    @cmpID varchar(10),
    @cmpName varchar(50), 
    @cmpDesc varchar(100),
    @cmpAddress varchar(50),
    @cmpType varchar(20),
    @cmpParent varchar(50)

AS
BEGIN

SET NOCOUNT ON; 

--INSERT Record
if @action = 'INSERT'
BEGIN
INSERT INTO cmpStructure(cmpID, cmpName, cmpDesc, cmpAddress, cmpType, cmpParent)
                        VALUES
                        (@cmpID, @cmpName, @cmpDesc, @cmpAddress, @cmpType, @cmpParent)
END 

--UPDATE record
else if @action = 'UPDATE'
BEGIN
UPDATE cmpStructure SET cmpName=@cmpName, cmpDesc=@cmpDesc, cmpAddress=@cmpAddress,      cmpType=@cmpType, cmpParent=@cmpParent WHERE cmpID=@cmpID
END

--DELETE Record
else if @action = 'DELETE'
BEGIN
DELETE FROM cmpStructure WHERE cmpID=@cmpID
END

--SELECT/SEARCH Record
else if @action = 'SELECT'
BEGIN
SELECT * FROM cmpStructure
END

END

这是c#代码

 // Add Record
    private void btnAdd_Click(object sender, EventArgs e)
    {
        try
        {
            SqlCommand commandp = new SqlCommand("spCmpStructure", dbcon.con);
            commandp.CommandType = CommandType.StoredProcedure;

            commandp.Parameters.AddWithValue("@action", ins);
            commandp.Parameters.AddWithValue("@cmpID", txtID.Text.ToString());
            commandp.Parameters.AddWithValue("@cmpName", txtCmp.Text.ToString());
            commandp.Parameters.AddWithValue("@cmpDesc", txtDetails.Text.ToString());
            commandp.Parameters.AddWithValue("@cmpAddress", txtAddress.Text.ToString());
            commandp.Parameters.AddWithValue("@cmpType", cbType.Text.ToString());
            commandp.Parameters.AddWithValue("@cmpParent", cbParent.Text.ToString());

            dbcon.openConnection();

            commandp.ExecuteNonQuery();

            dbcon.closeConnection();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            dbcon.closeConnection();
        }

    }
 // Update Record
    private void btnUpdate_Click(object sender, EventArgs e)
    {
        try
        {
            SqlCommand commandp = new SqlCommand("spCmpStructure", dbcon.con);
            commandp.CommandType = CommandType.StoredProcedure;

            commandp.Parameters.AddWithValue("@action", upd);
            commandp.Parameters.AddWithValue("@cmpID", txtID.Text.ToString());
            commandp.Parameters.AddWithValue("@cmpName", txtCmp.Text.ToString());
            commandp.Parameters.AddWithValue("@cmpDesc", txtDetails.Text.ToString());
            commandp.Parameters.AddWithValue("@cmpAddress", txtAddress.Text.ToString());
            commandp.Parameters.AddWithValue("@cmpType", cbType.Text.ToString());
            commandp.Parameters.AddWithValue("@cmpParent", cbParent.Text.ToString());

            dbcon.openConnection();

            int checkUpdate = commandp.ExecuteNonQuery();

            if (checkUpdate > 0)
            {
                MessageBox.Show(txtCmp.Text.ToString() + " Updated");
            }
            else
            {
                MessageBox.Show(txtCmp.Text.ToString() + " Update Failed");
            }

            dbcon.closeConnection();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally 
        {
            dbcon.closeConnection();
        }
    }
 // Remove Record
    private void btnRemove_Click(object sender, EventArgs e)
    {
        try
        {
            SqlCommand commandr = new SqlCommand("spCmpStructure", dbcon.con);
            commandr.CommandType = CommandType.StoredProcedure;

            commandr.Parameters.AddWithValue("@action", del);
            commandr.Parameters.AddWithValue("@cmpID", txtID.Text.ToString());

            dbcon.openConnection();

            int checkRemoved = commandr.ExecuteNonQuery();

            if (checkRemoved > 0)
            {
                MessageBox.Show(txtCmp.Text.ToString() + " Removed");
            }
            else
            {
                MessageBox.Show(txtCmp.Text.ToString() + " Removal Failed");
            }

            dbcon.closeConnection();
        }
        catch(Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            dbcon.closeConnection();
        }
    }

    private void companyStructure_Load(object sender, EventArgs e)
    {
        try
        {
            using (SqlCommand commandg = new SqlCommand("spCmpStructure", dbcon.con))
            {
                commandg.CommandType = CommandType.StoredProcedure;

                commandg.Parameters.AddWithValue("@action", sel);

                dbcon.openConnection();

                SqlDataReader dr = commandg.ExecuteReader();
                DataTable dt = new DataTable();
                dt.Load(dr);
                dgvCompany.DataSource = dt; 

                dbcon.closeConnection();
            }

        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            dbcon.closeConnection();
        }

        try
        {
            using (SqlCommand commands = new SqlCommand("spCmpStructure", dbcon.con))
            {
                commands.CommandType = CommandType.StoredProcedure;

                commands.Parameters.AddWithValue("@action", sel);

                dbcon.openConnection();

                SqlDataReader dr = commands.ExecuteReader();

                while (dr.Read())
                {
                    cbType.Items.Add(dr["cmpType"].ToString());
                    cbParent.Items.Add(dr["cmpParent"].ToString());
                }
                dbcon.closeConnection();
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }
        finally
        {
            dbcon.closeConnection();
        }

    }

Answer 1

必须传递存储过程中声明的所有参数，以避免此异常。

在btnRemove_Click方法中，您只传递了两个参数。所以你才会有例外。在存储过程中传递所有参数或定义可选参数，如下所示

@cmpID varchar(10)=null,
@cmpName varchar(50)=null, 
@cmpDesc varchar(100)=null,
@cmpAddress varchar(50)=null,
@cmpType varchar(20)=null,
@cmpParent varchar(50)=null

现在，这些参数被认为是可选的，因此不需要传递它。

Answer 2

将varchar替换为nvarchar。