0

here is an example mysql store procedure

CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);

INSERT INTO EMPLOYEE VALUES (0001, 'Clark', 'Sales');
INSERT INTO EMPLOYEE VALUES (0002, 'Dave', 'Accounting');
INSERT INTO EMPLOYEE VALUES (0003, 'Ava', 'Sales');

delimiter //
CREATE PROCEDURE employeeCount (IN id INT)
BEGIN
SELECT COUNT(*) FROM EMPLOYEE;
END//

delimiter ;

call employeeCount(2);

now is it possible to set default value of param id like this (IN id INT default 29), I have tried but it gives error saying

ERROR 1064 (42000) at line 17: 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 'DEFAULT 7)

I have searched google and even looked into mysql reference document but still unable to find anything about this, is it possible if yes then how?

taha malik
  • 1
  • 1
  • No, there are no default parameters in MySQL. – Barmar Jan 14 '23 at 19:24
  • any work around I can do? – taha malik Jan 14 '23 at 19:26
  • Not that I can think of. Why aren't you able to supply the parameter when you call the function? – Barmar Jan 14 '23 at 19:26
  • well ther are two reason first because I am learning mysql and want to know a work around, second my senior has asked me to di it that way. – taha malik Jan 14 '23 at 19:29
  • well no it still doesn't. the solutin they provided is checking if condition when begin procedure and passing empty string I dont want to pass anything, why is this still not added in this. – taha malik Jan 14 '23 at 19:35
  • the funny part is that chaatgpt is giving saying that it is possible CREATE PROCEDURE myProcedure(IN p_parameter VARCHAR(255) DEFAULT 'NULL') BEGIN -- stored procedure body END; – taha malik Jan 14 '23 at 19:42
  • but it does not work – taha malik Jan 14 '23 at 19:43
  • Honestly, I have no idea why anyone would use stored procedures in MySQL. There are so many deficiencies. No packages, no debugger, no code editor, no atomic deployment, etc. Add to that the tendency for feature requests to go unimplemented for years at a time. – Bill Karwin Jan 14 '23 at 19:48
  • then what can I use I have huge query which takes time if use it as view. – taha malik Jan 14 '23 at 19:49
  • A query does not take less time if it's in a stored procedure. Optimize the query with indexes, then execute the query from a client. – Bill Karwin Jan 14 '23 at 19:50
  • yes it does not take less time but in view I am checking data for employee count above 1k so it takes time, but in stored procedure I have passed employee_id of single employee when I need that of multiple employees at once with use of whereIN but how can I do this without stored procedure is still my question? – taha malik Jan 14 '23 at 20:05
  • 1
    @BillKarwin That's funny. A couple of weeks ago in another question, someone recommended using SP for everything, rather than putting the logic in the client applications (to minimize data transfer from the DB). I think they're crazy, since SP has no data structures and poor control structures. – Barmar Jan 14 '23 at 23:06
  • @Barmar, I accept that is a standard practice in other databases such as Oracle, Microsoft SQL Server, DB2... even to some extent in PostgreSQL. All of those have much more mature implementations of stored routines as a development environment. But in MySQL specifically, stored routines seem like a "checkbox" feature that was given an MVP implementation in 2005 and never touched again. – Bill Karwin Jan 14 '23 at 23:08

0 Answers0