CODEWITHSUNDEEP
mysqlvb.netstored-procedures
Meyka Jograt
Meyka Jograt

Reputation: 314

SELECT WITH CALL PROCEDURE MySQL

I got a Stored Procedure names getStocks that have a parameter. The code below is a example how I want to do with the query. Is it possible with the SELECT QUERY we can call a procedure?

PS I didn't use Function cuz I'm getting loading problem when I apply it in populating DataGridview in my VB.Net

BEGIN

SELECT ItemId, CatalogNumber, call getStocks(ItemId) AS quantity, 
Cost, Minimum, Maximum, TypeId, SupplierId FROM items;

END

Upvotes: 1

Views: 477

Answers (2)

CSTobey
CSTobey

Reputation: 1511

You cant do this. CALL is its own statement that you cant mix with SELECT in any way. If you cant use a UDF, you have to preform the integration manually.

It makes sense that a CALL cant be used like this when you consider that a CALL can optionally output a resultset. It might return a resultset, it might now. It might have one cell, one row, one column, or many rows/columns. The columns are not known at call time so the optimizer couldn't validate any JOINs to it if you put a call in the FROM and the because it can produce more than one cell (or nothing), you cant reliably put it in the SELECT. Additionally, stored procedures can have output variables, which also doesnt make sence in the context of a SELECT statement.

In other words, because the output of procedures is probably incompatible with any part of a SELECT query, the makers of mysql globally prevent their mixing.

Upvotes: 1

Robert Columbia
Robert Columbia

Reputation: 6408

You want to use a User-Defined Function (UDF) : http://dev.mysql.com/doc/refman/5.7/en/create-function-udf.html . Unlike stored procedures, UDF's may be called inline in a query.

Upvotes: 0

Related Questions