Show message in stored procedure

Question

How do I show a message from a stored procedure?

What is the right syntax to display a message saying whether or not rows exist?

In SQL Server it's PRINT to show a message bat in WORKBENCH...

CREATE PROCEDURE `new_proced` (
    in myid     int(3)
)
BEGIN
    if not exists (select id from table where id = myid)
then
    show message 'Row no exists';
else
    show message 'Row exists';
end if;
END

Tashinatashkent

Stored procedures can't "show messages". Then can return either an OUTPUT parameter OR a record set. It's up to your application to determine what to do with the results. That said, you can "print" a message which is similar to simply selecting some text: see #6912602 – Yolanda

Yes, but for this moment is not for aplication. I'm just testing my database. :) Thank's. – Tashinatashkent

Answer 1

Not entirely sure why you would want to do something like that, but you could do something like this: ...

then
  select 'YOUR MESSAGE HERE'  
else
  select 'YOUR OTHER MESSAGE HERE'
end if

Or you could select 1 or 0, might be a little better...

Yuu

Why would someone do that: Imagine you are working with large amount of data and you are using mysql terminal...and if you are also doing SQL-programming, there is a HIGH possibility that you will want to debug/show some output in some special cases, because with data so large, you just can't go through your data... BTW: I would do even SELECT 'YOUR MESSAGE' AS error_message; for better understanding of error in terminal/some sql admin :) – Antipater

Answer 2

There is no Proper output statement provide in MySQL like in Oracle, we have DBMS_OUTPUT.PUT_LINE. So, to display any messages on the console, you can use SELECT statement as:

SELECT message;

Eg: SELECT "WELCOME TO MySQL";

Aboutship

Answer 3

For debugging info from stored procedure in MySQL,there are following options through which you can do this.

1.Write into the file externally:

select "your_message" as log into outfile '/temp/brajesh.txt';

2.Use select command to print message:

select "result_message";

3.Use select command to print additional information with message:

select concat("Hello ! :", result);

4.Create addition table temp and push all message into it:

insert into temp select concat(result);

Example

drop procedure if exists display_name_procedure;
delimiter //
create procedure display_name_procedure(IN name_val varchar(65))
begin
declare result varchar(65);
set result := display_name_function(name_val);

create table if not exists temp (name_val varchar(65) not null);
insert into temp select concat(result);
select "penguin" as log into outfile '/temp/brajesh.txt';
select concat("Hello ! :", result);

end//

delimiter ;

Parturifacient

Answer 4

The full procedure is built in this code. You can modify this code per your requirement.

CREATE PROCEDURE insertData
    @Title nvarchar(max),
    @Releasedate datetime2(7),
    @genre nvarchar(max),
    @Price decimal(18,2)
AS
BEGIN
   
    IF NOT EXISTS (SELECT 1 FROM Movie WHERE Title = @Title)
    BEGIN
 
        INSERT INTO Movie (Title, Releasedate, Genre, Price)
        VALUES (@Title, @Releasedate, @genre, @Price);
    END
    ELSE
    BEGIN
   
        SELECT 'Already exists' AS Status;
    END
END

Valparaiso

Answer 5

DELIMITER //
 
create procedure 

insert_data_student1(student_name varchar(50),email varchar(50),group_id int)

 begin

     if not exists(select email from student where email="email")
     then
         insert into student(student_name,email,group_id) values (student_name,email,group_id);
     else
          select 'all reday exists';

     end if;
 end //
DELIMITER ;

Bridgetbridgetown

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Willhite