Stored Proc in sql that does not return the value

Question

My function isn't returning anything - strReturn is empty:

        try
        {
            SqlParameter[] parameter = new SqlParameter[]
            {
                new SqlParameter("@MerchantID", MercahntID),
                new SqlParameter("@LoactionID", LoactionID)
            };

            SqlHelper.ExecuteNonQuery(DbConnString, System.Data.CommandType.StoredProcedure, "GetMerchantLocationZip", parameter);

            return strReturn;
        }
        catch (Exception ex)
        {
            LogError("Error Occurred When Retrieving Mercahnt Location Zip: MercahntID:" + MercahntID.ToString(), ex);
            return strReturn;
        }
    }

When I execute this stored proc using 'exec GetMerchantLocationZip (3333, 373773)' I get the correct zipcode in SQL. Why don't I get it in Visual Studio?

Create PROCEDURE [dbo].[GetMerchantLocationZip](
@MerchantID bigint,
@LoactionID bigint)

AS 

Begin

Select Zip FROM Merchant_Location 
where MerchantID=@MerchantID AND LocationID =@LoactionID

End

I am learning, so apologies if it's a obvious error. Thanks all!

Answer 1

You don't appear to be assigning anything to strReturn anywhere. You would also need to use ExecuteScalar to retrieve the value from a single row, single column result set.

strReturn = SqlHelper.ExecuteScalar(...) as string;

Or an OUTPUT parameter with ExecuteNonQuery.

Answer 2

You're not getting results because you're not executing the code as a Query.

You're calling SqlHelper.ExecuteNonQuery() which doesn't return any results.

It looks like you're using the SqlHelper application block, so I think the code you want would be (if you're returning multiple rows in the query):

DataSet ds = SqlHelper.ExecuteDataSet(DbConnString,
                                      CommandType.StoredProcedure,
                                      "GetMerchantLocationZip",
                                      parameter);

ds will then contain the results of the query.

If you're trying to retrieve a single value from the database rather than a set of rows, then your code would be:

object zip = SqlHelper.ExecuteScalar(DbConnString,
                                     CommandType.StoredProcedure,
                                     "GetMerchantLocationZip",
                                     parameter);

Answer 3

You are calling ExecuteNonQuery which only returns the number of rows affected. Try this:

var zipCode = SqlHelper.ExecuteScalar(DbConnString, System.Data.CommandType.StoredProcedure, "GetMerchantLocationZip", parameter);

Answer 4

Check your spelling!! You're probably throwing an exception in the SQL statement because you've misspelled "Merchant" or "Location" somewhere (or many places).

And, as others have pointed out, you probably want this:

return SqlHelper.ExecuteScalar(DbConnString, 
    System.Data.CommandType.StoredProcedure, 
    "GetMerchantLocationZip", parameter);

Answer 5

You are using ExecuteNonQuery which doesn't return results, it's just going to return the number of rows updated. You want to execute a reader.

Answer 6

strReturn isn't being set, and you need to call ExecuteScalar instead of ExecuteNonQuery.

Answer 7

You probably want to call the ExecuteScalar instead of ExecuteNonQuery as that is expected to have no return.

Perhaps the following code

 var zipObject = SqlHelper.ExecuteScalar(DbConnString, System.Data.CommandType.StoredProcedure, "GetMerchantLocationZip", parameter);

 return (string)zipObject;