CODEWITHSUNDEEP
mysqlstored-procedures
Frostless
Frostless

Reputation: 848

store procedure to count number of records

I'm supposed to:

  1. create a stored procedure to count the number of records of a table(showsite)

  2. run the procedure to display the number of records in the table Below are the table structure and its data:

    show_id INT not null, name varchar(20) not null, address varchar(40) not null, UNIQUE (show_id), PRIMARY KEY (show_id)

    Show_id Name Address 1 Dubbo 23 Wingewarra St, Dubbo 2 Young 13 Cherry Lane, Young 3 Castle Hill Showground Rd, Castle Hill 4 Royal Easter PO Box 13, GPO Sydney

My code for question one is :( have not created code for question 2 as stuck with 1 and it drives me crazy)

DELIMITER//
create procedure site_sp
as
begin
select count(*) from showsite;
end//
DELIMITER;

but when I tried to run it in mysql it always returned with syntax error, can somebody shed some light on it?

Upvotes: 0

Views: 1512

Answers (1)

an33sh
an33sh

Reputation: 1134

DELIMITER //
DROP PROCEDURE IF EXISTS site_sp //
CREATE PROCEDURE site_sp()
BEGIN
SELECT COUNT(1) FROM showsite;
END//
DELIMITER ;

I prefer count(1) over count(*)

Upvotes: 2

Related Questions