C#存储过程或函数需要未提供的参数 [英] C# Stored procedure or function expects parameter which is not supplied

问题描述

我对C#相当陌生，我正尝试在我的数据库中设置对一个参数的存储过程的调用.

I am fairly new to C# and I'm trying to set up call to a stored procedure in my database which takes one parameter.

我收到错误消息过程或函数'SP_getName'期望提供参数'@username'，但未提供."

I get the error "Procedure or function 'SP_getName' expects parameter '@username', which was not supplied. "

当我向其提供参数并通过SQL Management Studio运行它时，我的存储过程可以正常工作.

My Stored procedure works ok when I supply it with the parameter and I run it via SQL management studio.

GO

DECLARE @return_value int

EXEC    @return_value = [dbo].[SP_getName]
    @username = 'bob101'

SELECT  'Return Value' = @return_value

GO

但是，当我尝试调用它时，错误与我如何传入参数有关，但我无法发现问题所在.

However when I try and call it the error is with how I'm passing the parameter in, but I can't spot what the issue is.

           //create a sql command object to hold the results of the query
            SqlCommand cmd = new SqlCommand();

            //and a reader to process the results
            SqlDataReader reader;

            //Instantiate return string
            string returnValue = null;

            //execute the stored procedure to return the results
            cmd.CommandText = "SP_getName";

            //set up the parameters for the stored procedure
            cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = "bob101";

            cmd.CommandType = CommandType.Text;
            cmd.Connection = this.Connection;

            // then call the reader to process the results
            reader = cmd.ExecuteReader();

在发现我的错误方面的任何帮助将不胜感激！

Any help in spotting my error would be greatly appreciated!

我也尝试着看这两篇文章，但是我没有任何运气:

I've also tried looking at these two posts, but I haven't had any luck:

存储过程或函数期望的参数不是提供

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

谢谢！

推荐答案

您已声明:

cmd.CommandType = CommandType.Text;

因此，您只需执行以下操作即可:

Therefore you are simply executing:

SP_getName

之所以起作用，是因为它是批处理中的第一条语句，因此您可以在不使用 EXECUTE 的情况下调用该过程，但实际上并没有包括该参数.将其更改为

Which works because it is the first statement in the batch, so you can call the procedure without EXECUTE, but you aren't actually including the parameter. Change it to

cmd.CommandType = CommandType.StoredProcedure;

或者您可以将CommandText更改为:

Or you can change your CommandText to:

EXECUTE SP_getName @username;

作为旁注，您应该避免在存储过程中使用前缀"sp_"

还有一个补充说明是将 using 与IDisposable对象一起使用，以确保正确处理它们:

And a further side note would be to use using with IDisposable objects to ensure they are disposed of correctly:

using (var connection = new SqlConnection("ConnectionString"))
using (var cmd = new new SqlCommand("SP_getName", connection))
{
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = "bob101";
    connection.Open();
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            // Do something 
        }
    }
}

这篇关于C#存储过程或函数需要未提供的参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！