MS-SQL 存储过程不适用于空参数

本文关键字：参数 适用于 不适用 存储过程 MS-SQL | 更新日期: 2023-09-27 18:37:26

我有一个输入"姓名"和"电话"的表单。

当它们有值时，一切正常：使用存储过程将记录插入数据库，spFoo：

String sp = "spFoo 'test name','test phone'";
OdbcCommand command = new OdbcCommand(sp, connection);
command.CommandType = System.Data.CommandType.StoredProcedure;
connection.Open();
Response.Write(command.ExecuteNonQuery().ToString());
  • 适用于应用程序
  • 在管理工作室工作

但是当它们没有值时，我在应用程序中得到 -1 作为响应：

String sp = "spFoo '',''";
  • 在应用程序中不起作用 （-1）
  • 在管理工作室工作

我希望用户能够在没有任何输入的情况下创建记录。

为什么这在管理工作室中有效，但在应用程序上不起作用？

更新：我在存储过程中的参数中添加了默认值，但它不起作用;我在代码中给出了空字符串"NULL"作为值，但仍然没有运气。这是服务器设置还是不允许空变量的东西？

MS-SQL 存储过程不适用于空参数

您需要从存储过程中删除SET NOCOUNT ON;。

从文档中：

停止显示受 Transact-SQL 语句或存储过程影响的行数计数的消息作为结果集的一部分返回。

正如其他人指出的那样，您也应该参数化您的查询，但-1是由NOCOUNT ON引起的。

编辑

我意识到这不是你要问的，但是要对 ODBC 使用参数化查询，您需要根据此处的文档使用 ? 的作为序号占位符。例如：

using (OdbcConnection connection = new OdbcConnection(connectionString))
{
    string sp = "{call spFoo (?, ?)}";
    using (OdbcCommand command = new OdbcCommand(sp, connection))
    {
        command.CommandType = System.Data.CommandType.StoredProcedure;
        connection.Open();
        //the order here is important, the names are not!
        command.Parameters.Add("@name", OdbcType.VarChar).Value = "test name";
        command.Parameters.Add("@phone", OdbcType.VarChar).Value = "test phone";
        Console.WriteLine(command.ExecuteNonQuery().ToString());
    }
}

从代码调用存储过程时，应使用命令上的 Parameters 属性。试试这个：

String sp = "spFoo";
command.Parameters.Add("@name", "test name");
command.Parameters.Add("@phone", "test phone");

正如JimmyV所说，你应该使用这个命令。Parameters.Add 方法来设置参数，只要未指定参数值，就传入 null。要解决您关于错误"过程或函数'spFoo'期望未提供的参数'@name'的评论，您还需要修改存储过程以在未提供参数时使用默认值（例如 null）：

CREATE PROCEDURE MyStoredProcedure
    @foo int = null
AS
BEGIN
...
END

很抱歉没有在上面的帖子中添加评论。口碑不够！

不应以当前的方式调用存储过程。您应该使用参数。您的代码容易受到 SQL 注入的影响。

切勿字符串连接用户输入的值。

您应该拥有的是一个类似的存储过程设置：

CREATE PROCEDURE spFoo
    @name varchar(50) = 'Jim', -- default
    @phone varchar(50) = null -- optional
AS
BEGIN
    SET NOCOUNT ON;
    -- INSERT STATEMENT
END
GO

然后在代码中提供参数：

string name = this.nameTextBox.Text;
string phone = this.phoneTextBox.Text;
if (string.IsNullOrWhiteSpace(name))
    name = null;
if (string.IsNullOrWhiteSpace(phone))
    phone = null;
SqlConnection connection = new SqlConnection(@"<connection string>");
using (SqlCommand command = connection.CreateCommand())
{
    command.CommandType = CommandType.StoredProcedure;
    // leave this as the stored procedure name only
    command.CommandText = "spFoo";
    // if name is null, then Jim gets passed (see stored procedure definition)
    // if phone is null, then null gets passed (see stored procedure definition)
    command.Parameters.AddWithValue("@name", name);
    command.Parameters.AddWithValue("@phone", phone);
    try
    {
        connection.Open();
        int result = command.ExecuteNonQuery();
        Console.WriteLine(result);
    }
    finally
    {
        if (connection.State != ConnectionState.Closed)
            connection.Close();
    }
}

我不确定你为什么使用 Odbc 命名空间对象，因为听起来你正在使用 MS-SQL。您应该使用 System.Data.SqlClient 命名空间中的对象。

实际问题的答案很可能涉及执行类似于以下内容的脚本（而不是存储过程）：

DECLARE @RC int
DECLARE @name varchar(50)
DECLARE @phone varchar(50)
-- TODO: Set parameter values here.
EXECUTE @RC = spFoo
   @name,
   @phone
GO

不建议这样做。

相关文章：