多个查询是单个存储过程

问题描述

我正在尝试对 INSERT、UPDATE、DELETE 和 SELECT 语句使用单个存储过程.单独它们工作正常，但结合使用时，我在上述所有查询中收到以下错误.

I am trying to use a single stored procedure for INSERT, UPDATE, DELETE and SELECT statements.Separately they work fine but when combined I get the following error on all the above queries.

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

Procedure or function expects parameter which was not supplied.

这是程序.

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();
        }

    }

推荐答案

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

You must pass all parameter that is declared in stored procedure to avoid this exception.

在您的 btnRemove_Click 方法中，您只传递了两个参数.这就是为什么你得到例外.像这样在存储过程中传递所有参数或定义可选参数

in your btnRemove_Click method you had only pass two parameter. that's why you getting exception. Either pass all parameter or define optional parameter in stored procedure like this

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

现在这些参数被认为是可选的，所以你不需要传递它.

Now these parameter is considered as optional so you do not need to pass it.

这篇关于多个查询是单个存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！