Passing parameter to SQL Server stored procedure

Question

I am trying to pass a parameter to a stored procedure in my application but I don't know what I am doing wrong. I get an error

Procedure or function 'usp_getBorrowerDetails' expects parameter '@BookID', which was not supplied.

while I am passing and I did many things but still didn't find the solution.

This is my code:

IDataReader reader = null;

SqlCommand command = new SqlCommand();

try
{
    SqlConnection connection = GetDBConnection();

    command.CommandType = CommandType.StoredProcedure;
    command.CommandText = Constants.SP_BookBorrowerDetails;
    command = new SqlCommand(command.CommandText, connection);

    command.Parameters.AddWithValue("@BookID", bookID);

    reader = base.ExecuteReader(command);
}
catch (System.Data.SqlClient.SqlException ex)
{
    throw new Exception("Oops! Something went wrong.");
}

Below is my stored procedure:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[usp_getBorrowerDetails]
    @BookID INT
AS
    SELECT 
        Name, Mobile, ReturnDate 
    FROM
        BorrowerDetails 
    INNER JOIN
        BookDetails ON BookDetails.CurrentBorrowerID = BorrowerDetails.ID    
    WHERE
        BookDetails.BookID = @BookID
GO

If I run any stored procedure that does not requires any parameter, it works fine. Issue is only coming when I am adding parameter.

Do not use AddWithValue https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ — GuidoG, Apr 11 '18 at 14:37
Not an answer but you really need to wrap your connection in a USING clause. As coded your connection is not being properly closed and disposed which can lead to the connection pool being consumed. — Sean Lange, Apr 11 '18 at 14:41
@GuidoG I have used this command.Parameters.Add("@BookID", SqlDbType.Int).Value = bookID;. But still getting same issue. — kamran Ladhani, Apr 11 '18 at 14:44

score 7 · Accepted Answer · answered Apr 11 '18 at 14:47

You're redefining command to a brand new SqlCommand after setting the CommandType and CommandText, meaning it will be treated as plain SQL, rather than a stored procedure. Create it once in the appropriate place.

IDataReader reader = null;
SqlCommand command;

try
{
    SqlConnection connection = GetDBConnection();

    command = new SqlCommand(Constants.SP_BookBorrowerDetails, connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@BookID", bookID);

    reader = base.ExecuteReader(command);
}
catch (System.Data.SqlClient.SqlException ex)
{
    throw new Exception("Oops! Something went wrong.");
}

As an aside, you should probably also look at not just keeping the connection open, but instead getting the results within the one method, rather than relying on the calling code to manage the connection.

score 4 · Answer 2 · answered Apr 11 '18 at 14:47

4

One of the problems is the order of your code. You are setting your command to a new SqlCommand. When this happens the default CommandType is Text.

command = new SqlCommand(command.CommandText, connection);
command.CommandType = CommandType.StoredProcedure;
command.CommandText = Constants.SP_BookBorrowerDetails;

You should first create the command, then set the properties.

answered Apr 11 '18 at 14:47

Sean Lange

33,028
3
25
40

@PeterB not sure what you mean. – Sean Lange Apr 11 '18 at 14:50
Sorry, first line threw me off, it says `new SqlCommand(command.CommandText ...) ` but it should use `Constants.SP_BookBorrowerDetails` – Peter B Apr 11 '18 at 14:51
Ahh I see what you mean. I just rearranged the lines the OP posted. Not really sure why they have a Constants object to hold string values but whatever. Maybe it reads from a resx or something. Probably better to just not include it in the constructor since it is being set explicitly later. Doing it twice is kind of silly. :) – Sean Lange Apr 11 '18 at 14:54

Marc Gravell · Answer 3 · 2018-04-11T14:48:21.033

2

One possibility here is that bookID is null.

Convert.ToString(null) returns: null.

A parameter with a null value is not attached. You need to use DBNull.Value for that.

But: there's also no need to make it a string anyway.

Consider using: (object)bookId ?? DBNull.Value as the value instead, which should fix both of these issues.

Edit: as noted in a comment, you're also setting the CommandType too early, and then replacing the command object; setting the CommandType must be done after you've new-d the variable.

Note: these are just two of many things that "dapper" would help you with; consider:

var reader = connection.ExecuteReader(
      "usp_getBorrowerDetails", new { BookID = bookID },
      commandType: CommandType.StoredProcedure);

(although usually I'd advocate dapper's Query<T> methods instead - they do a lot more for you, too)

edited Apr 11 '18 at 14:48

answered Apr 11 '18 at 14:46

Marc Gravell

1,026,079
266
2,566
2,900

He should also set the commandtype and text after creating a new command, not before – GuidoG Apr 11 '18 at 14:47
I updated code and removed convert.ToString. Still getting same issue – kamran Ladhani Apr 11 '18 at 14:47
@kamranLadhani see GuidoG's point there too; that's another very likely cause! – Marc Gravell Apr 11 '18 at 14:49
@GuidoG even more reason to prefer tools like "dapper" (see edit); you can't mess up code that you don't have to write :) – Marc Gravell Apr 11 '18 at 14:49

score 0 · Answer 4 · answered Apr 11 '18 at 15:57

I believe you have declared Constants.SP_BookBorrowerDetails as "EXEC [dbo].[usp_getBorrowerDetails]", it doesn't have the expected input parameter "@BookID".

Command.Parameters.AddWithValue which will only accepts the parameter name & value which needs to be passed.But still it would expect the parameter in the Constants.SP_BookBorrowerDetails syntax because while executing the command text the list of parameters available in the "Command.Parameters" needs the mapping parameter which has the same name.

using (SqlConnection connection = new SqlConnection("SQL connection string"))
        {
            connection.Open();
            try
            {
                SqlCommand command = new SqlCommand("EXEC [dbo].[usp_getBorrowerDetails] @BookID", connection);
                command.CommandType = CommandType.StoredProcedure;
                command = new SqlCommand(command.CommandText, connection);
                command.Parameters.AddWithValue("@BookID", 1);

                IDataReader reader = command.ExecuteReader();
            }
            catch (SqlException ex)
            {
                throw new Exception("Oops! Something went wrong." + ex.Message);
            }
        }

Please check above code snippet.

Please revert back if any issue arises.

score 0 · Answer 5 · answered Jun 25 '20 at 03:57

It's work for me,

connection = new SqlConnection(connectionString);
connection.Open();
SqlCommand  com  = new SqlCommand("usp_getBorrowerDetails", connection);
com.CommandType = CommandType.StoredProcedure;

com.Parameters.AddWithValue("@BookID", bookID);

IDataReader reader = com.ExecuteReader();

while (reader.Read())
{
   
}

reader.Close();
connection.Close();

Passing parameter to SQL Server stored procedure

5 Answers5

Linked