Entity Framework stored procedure: Import to complex type function, error retrieving column information

Question

I am working on a project in VS2012 using a MySQL and Entity Framework 5.0.0 .

When I try to create a new type of Complex from a stored procedure, I get an error when I click the "Get column information" button:

The selected stored procedure or function does not return columns.

My stored procedure code is as follows:

 DROP PROCEDURE IF EXISTS SearchAlgemeenSet; Delimiter // CREATE PROCEDURE SearchAlgemeenSet(IN in_searchQuery VARCHAR(255)) BEGIN SELECT Blokken, Jaargang, Werk_Uren FROM algemeensets WHERE Blokken LIKE in_searchQuery OR Jaargang LIKE in_searchQuery OR Werk_Uren LIKE in_searchQuery; END // Delimiter ; 

I'm sure it returns columns if the in_searchQuery parameter has a match.

In my research, I found many solutions for the Microsoft SQL database. But none of these solutions apply to the MySQL database.

How to solve this?

Answer 1

I am sure that it returns columns if the in_searchQuery parameter has a match.

Since you are not using any in_searchQuery for partial searches, if it does not find an exact match, no rows will be returned for the in_searchQuery value. For a partial match, you need to use the wildcard symbol '%' with the value in_searchQuery .

The modified procedure should look like this:

 DROP PROCEDURE IF EXISTS SearchAlgemeenSet; Delimiter // CREATE PROCEDURE SearchAlgemeenSet(IN in_searchQuery VARCHAR(255)) BEGIN set @search_criteria := concat( '%', in_searchQuery, '%' ); SELECT Blokken, Jaargang, Werk_Uren FROM algemeensets WHERE Blokken LIKE @search_criteria OR Jaargang LIKE @search_criteria OR Werk_Uren LIKE @search_criteria; END; // Delimiter ; 

If there are no matches in the search criteria and an empty set will be returned.
In your scripting language, you should check in advance whether the procedure returned any results or not. Based on this, you can show a message that the data was not found or you can perform other actions.