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.

Answer 1

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.

Answer 2

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.