MySQL Forums
Forum List  »  Stored Procedures

Using temp table in Stored procedure help
Posted by: S K
Date: May 25, 2020 10:50AM

I am using mysql 5.7.

Here is my stored procedure

CREATE PROCEDURE SP_ExposureByStock()
BEGIN
CREATE TEMPORARY TABLE tempExposure AS
SELECT t.ticker
,cq.companyName
,t.quantity
,cq.price
,cq.price * t.quantity
,cq.price * t.quantity - cb.costBasisPerUnit * t.quantity
FROM transactions t
LEFT JOIN currentQuotes cq
ON t.ticker = cq.ticker
LEFT JOIN costBasis cb
ON t.ticker = cb.ticker AND t.brokerage = cb.brokerage AND t.accountNumb = cb.accountNumb
;
SELECT * FROM tempExposure;
END

I get an error as follows

Error
SQL query: Documentation


CREATE PROCEDURE SP_ExposureByStock()
BEGIN
CREATE TEMPORARY TABLE tempExposure AS
SELECT t.ticker
,cq.companyName
,t.quantity
,cq.price
,cq.price * t.quantity
,cq.price * t.quantity - cb.costBasisPerUnit * t.quantity
FROM transactions t
LEFT JOIN currentQuotes cq
ON t.ticker = cq.ticker
LEFT JOIN costBasis cb
ON t.ticker = cb.ticker AND t.brokerage = cb.brokerage AND t.accountNumb = cb.accountNumb
MySQL said: Documentation

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 14

Options: ReplyQuote


Subject
Views
Written By
Posted
Using temp table in Stored procedure help
852
S K
May 25, 2020 10:50AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.