How to Have a Default Parameter for a MySQL Stored Procedure

Is it possible to have a default parameter for a mysql stored procedure?

It's still not possible.

Adding parameters with NULL default value for MySQL stored procedure

According to this answer MySQL does not support true 'default parameters' in functions or stored procedures.

This answer on the same page provides a solution that may work for you.

MySQL Stored Procedure with multiple optional parameters

In MySQL, the variable @Usario is not the same object as the procedure IN parameter Usario. They are different variables. You cannot reference @Usario to get the value of the IN parameter Usario.

You should name your parameter something distinct from the column name you want to compare it to, then just use it in the query without a @ character. For example, one could use a naming convention to prefix the parameter names with "p".

CREATE DEFINER=`conciliacion`@`%` PROCEDURE `BuscarRRNormal`(IN `pFechaDesde` DATE, IN `pFechaHasta` DATE, IN `pUsuario` varchar(255))
BEGIN

SELECT IDS, Fecha_Recarga, Usuario, Monto, Operador
FROM transaccionesrr
WHERE (Fecha_Recarga BETWEEN pFechaDesde AND pFechaHasta)
AND (pUsuario IS NULL OR Usuario = pUsuario);

END

Is there any way to have a default integer passed inside a stored procedure?

The syntax you show makes me think you are using MySQL or MariaDB.

These implementations don't support a feature for default values for procedure parameters. This has been requested in MySQL: https://bugs.mysql.com/bug.php?id=15975 But so far, it is not supported.

You're using the best workaround I know of, to set the parameter to your default value if it is NULL.

Another way of coding this is to use the COALESCE() function:

SET rank = COALESCE(rank, 3);

It's just another way to achieve the same thing that your IF/THEN code does.

How to create a Stored Procedure with Optional DateTime Parameters in mysql

If you want to retrieve all records when you pass the null value, you can use the IF... ELSE... Statement to determine what your Stored Procedure will select.

Check the following SP:

CREATE PROCEDURE GETDT(
in startDate DATETIME,
in endDate DATETIME
)
BEGIN
if startDate is null or endDate is null then
SELECT * FROM test;
else
SELECT * FROM test
WHERE
test.sdate >= startDate
AND
test.edate <= endDate;
end if;
END

See the result from db-fiddle.



Related Topics



Leave a reply



Submit