Search code examples
c#mysqldatabaseroutines

MySQL stored login procedure [return tables]

I'm trying to create a simple login for an ASP.net web application where I use MySQL as database and ODBC as a connector. Now I would like to prevent SQL injections by calling MySQL routines instead of application-side SQL.

Traditionally, I can run an SQL statement and recieve a table result, which I can store in a data class. This way, I have always comfortable access to e.g. the users properties like roles etc. without querying the database again. Now with mysql procedures or functions, it seems I can only return simple values like string, int or bit instead of full tables. So when I like to have the full table, do I have to run 2 queries? One as a stored function that returns e.g. the user id if the login was successful, another one for the users information? Or are functions capable of returning full tables?

Solution

  • It's not possible to write Table-Valued Stored procedure in MySQL Server. MySQL Stored Function returns single values only.

    However, if the situation demands a Function to return a set of Data, you have only two choices:

    • You may create a string using some separators with your result set and return it to the caller.
    • Another good alternative is to use Temporary Table.

    More details here.

    And please refer the mysql doc too.