Set NULL parameters in MySQL stored procedure

Question

I'm trying to convert a stored procedure from MS SQL to MySQL, so in MSSQL i'm using parameters with default value in NULL

`ALTER PROCEDURE [dbo].[sp_GLB_Users_S]
(
    @selType    int     =NULL,
    @ID     int     =NULL,
    @LoginID    varchar(20) =NULL,
    @UserCode   varchar(50) =NULL,
    @UserCategory   int     =NULL,
    @UserName   varchar(150)    =NULL,
    @EMail      varchar(100)    =NULL,
    @IsActive   bit     =NULL
)`

How to use NULL accepted parameters in MySQL

`CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_GLB_Users_S`(

IN selType  INTEGER,
IN ID       INTEGER,
    IN LoginID  VARCHAR(20),
    IN UserCode     VARCHAR(50),
    IN UserCategory INTEGER,
    IN UserName     VARCHAR(150),
    IN EMail    VARCHAR(100),
    IN IsActive     BOOLEAN
)`

possible duplicate of [Adding parameters with NULL default value for MySQL stored procedure](http://stackoverflow.com/questions/14091073/adding-parameters-with-null-default-value-for-mysql-stored-procedure) — Pang, Aug 21 '14 at 07:03

score 4 · Accepted Answer · answered Nov 13 '13 at 04:35

4

The short answer is MySQL still lacks support for default values for routine parameters even in 5.7.

There is a workaround by using session variables instead of actual parameters, but personally IMHO it doesn't worth it.

answered Nov 13 '13 at 04:35

peterm

91,357
15
148
157

Set NULL parameters in MySQL stored procedure

1 Answers1