多个查询是单个存储过程 [英] Multiple queries is single stored procedure
本文介绍了多个查询是单个存储过程的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试对 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.
这篇关于多个查询是单个存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
