CODEWITHSUNDEEP
mysqlsqlspring-bootjpastored-procedures
Himanshu Ranjan
Himanshu Ranjan

Reputation: 302

How to return a String value from a Stored Procedure in MySQL?

I want to return a value from Stored Procedure which I can use in my Spring Boot server in JPA repository. Here's my Stored Procedure,I want to know how do I return String from the Procedure.

DELIMITER //

CREATE PROCEDURE GetAllProducts(out query varchar(20))
BEGIN
    SET query=SELECT title FROM course where description="just a course";
RETURN @query;       
END //

DELIMITER ;

I am selecting 1 value from the query table to return it.

Can someone Help? Thanks!

Upvotes: 0

Views: 1164

Answers (2)

Himanshu Ranjan
Himanshu Ranjan

Reputation: 302

Got the Solution,Its working now!

Just tweaked the Procedure a little bit:

DELIMITER //
CREATE PROCEDURE GetAllProdcuts(OUT output VARCHAR(50))
BEGIN
SELECT title into output
FROM course 
WHERE description="just a course";
END //

Upvotes: 0

Akina
Akina

Reputation: 42834

If you want to use your SP as datasource (like you execute SELECT, not CALL, in external program) you must select to output stream, not to variable:

CREATE PROCEDURE GetAllProducts()
SELECT title 
FROM course 
WHERE description="just a course";

Upvotes: 1

Related Questions