2

I'm new to C# and I have a stored procedure in SQL Server:

DECLARE @return_value int

EXEC    @return_value = [dbo].[sp_getUserInfo]
        @TelNo = N'2537743007'

SELECT  'Return Value' = @return_value

GO

and try call that with this code:

using (SqlConnection con = new SqlConnection("Data Source=SERVICE;Initial Catalog=InvokeADSL;Integrated Security=True"))
{
    using (SqlCommand cmd = new SqlCommand("sp_getUserInfo", con))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        //cmd.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = txtFirstName.Text;
        cmd.Parameters.Add("@TelNo", SqlDbType.NVarChar).Value = "253774300";

        con.Open();

        reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            Console.WriteLine(reader["return_value"].ToString());
        }
    }
}

but I get an error:

System.IndexOutOfRangeException: return_value
at System.Data.ProviderBase.FieldNameLookup.GetOrdinal(String fieldName) at System.Data.SqlClient.SqlDataReader.GetOrdinal(String name)
at System.Data.SqlClient.SqlDataReader.get_Item(String name)
at ConsoleApplication1.Program.Main(String[] args)

How can I solve that? Thanks.

This is my stored procedure in SQL Server: link to code

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
chert chertopert
  • 197
  • 3
  • 14
  • This exception means that you're trying to access a collection item by index, using an invalid index. An index is invalid when it's lower than the collection's lower bound or greater than or equal to the number of elements it contains. – King Reload May 03 '17 at 06:14
  • @KingReload How can handle collection? – chert chertopert May 03 '17 at 06:16
  • Use index position instead of name. reader[0].ToString() – Thangadurai May 03 '17 at 06:19
  • Side note: you should **not** use the `sp_` prefix for your stored procedures. Microsoft has [reserved that prefix for its own use (see *Naming Stored Procedures*)](http://msdn.microsoft.com/en-us/library/ms190669%28v=sql.105%29.aspx), and you do run the risk of a name clash sometime in the future. [It's also bad for your stored procedure performance](http://www.sqlperformance.com/2012/10/t-sql-queries/sp_prefix). It's best to just simply avoid `sp_` and use something else as a prefix - or no prefix at all! – marc_s May 03 '17 at 06:19
  • http://stackoverflow.com/a/20940980/7707749 here's a whole explenation about what could cause this problem ;) try solve it yourself for learning purposes, I'm sure you'll figure it out – King Reload May 03 '17 at 06:21
  • Double check the final `SELECT` in your stored procedure. Shouldn't it be just `SELECT @return_value`? – Chris Dunaway May 03 '17 at 17:46

2 Answers2

1

Well, from the code you're showing as to how to call this stored procedure in T-SQL, it seems that the value is being returned from the stored procedure via a RETURN statement - not as a result set (SELECT ....).

Therefore, you need to read the return value of the stored procedure - not a result set using ExecuteReader():

using (SqlCommand cmd = new SqlCommand("sp_getUserInfo", con))
{
    cmd.CommandType = CommandType.StoredProcedure;

    cmd.Parameters.Add("@TelNo", SqlDbType.NVarChar).Value = "253774300";

    con.Open();

    // get the value from the RETURN statement in the stored procedure
    int returnValue = cmd.ExecuteNonQuery();
    Console.WriteLine("Return value: {0}", returnValue); 
}

Update: after consulting the stored procedure code, the result is in fact being returned by a SELECT (as a result set) - so you do need to use ExecuteReader - but that column just doesn't have a name, you so cannot access it using :

while (reader.Read())
{
    Console.WriteLine(reader["return_value"].ToString());
}

but you need to instead use the numerical index:

while (reader.Read())
{
    Console.WriteLine(reader[0].ToString());
}
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

If your stored procedure just returns one value then use var res = cmd.ExecuteScalar(); instead of reader = cmd.ExecuteReader();.
But if your stored procedure return table then use SqlDataAdapter with your current command like this SqlDataAdapter DA = new SqlDataAdapter(cmd); and then DA.Fill(dt);

Yashar Aliabbasi
  • 2,663
  • 1
  • 23
  • 35