Monday, September 27, 2010

how to get the returned value from stored procedure in asp.net

 private int GetReturnedValueFromStoredProcedure(string storedProcedureName, string connectionString, string paramValue)
        {
            SqlConnection connection = new SqlConnection(connectionString);

            // Command - specify as StoredProcedure
            SqlCommand command = new SqlCommand(storedProcedureName, connection);
            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add("@orgID", System.Data.SqlDbType.NVarChar).Value = paramValue;

            // Return value as parameter
            SqlParameter returnValue = new SqlParameter("returnVal", SqlDbType.Int);
            returnValue.Direction = ParameterDirection.ReturnValue;
            command.Parameters.Add(returnValue);

            // Execute the stored procedure
            connection.Open();
            command.ExecuteNonQuery();
            connection.Close();

            return Convert.ToInt32(returnValue.Value);
        }