Syntax error when calling a stored procedure. I can't see what the problem is

Question

I am having trouble calling the following stored procedure. When I call ExecuteReader, I am getting the error 'Incorrect syntax near 'GetAverages2'. I can create the stored procedure and call it from TSQL. I can't seem to get it working from ADO:

CREATE PROCEDURE GetAverages2
    @CompanySize INT,
    @Q1         FLOAT OUT,
    @Q2         FLOAT OUT
AS  
    SELECT @Q1 = 1, @Q2 = 2
GO

    string connectionString = ConfigurationManager.ConnectionStrings["MyDbConnection"].ConnectionString;
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = new SqlCommand("GetAverages2", connection))
        {
            command.Parameters.Add("@CompanySize", System.Data.SqlDbType.Int).Value = int.Parse(Request.QueryString["CompanySizeId"]);
            command.Parameters.Add("@Q1", System.Data.SqlDbType.Float).Direction = System.Data.ParameterDirection.Output;
            command.Parameters.Add("@Q2", System.Data.SqlDbType.Float).Direction = System.Data.ParameterDirection.Output;
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();

Matt Hamilton · Accepted Answer · 2009-09-09 10:13:30Z

2

The SqlCommand.CommandType defaults to CommandType.Text, so it's trying to execute the text "GetAverages2" as a raw SQL statement. Add this line just after you create the command:

command.CommandType = CommandType.StoredProcedure;

answered Sep 9, 2009 at 10:13

Matt Hamilton

205k61 gold badges395 silver badges321 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jeremy S Over a year ago

I just fell into the same trap. I'm so used to using stored procedures, I can't imagine writing raw SQL. FYI, in SQL 2008 the error message returned in this scenario has changed to "Procedure or function '<proc name>' expects parameter '<param name>', which was not supplied".

Collectives™ on Stack Overflow

Syntax error when calling a stored procedure. I can't see what the problem is

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related