Home > Enterprise >  Enable NULL in parameters in stored procedure MYSQL
Enable NULL in parameters in stored procedure MYSQL

Time:04-27

Hey I've got this procedure. I want to enable not to put anything in the parameters (ImputDate and AdmissionNumber) and when that happend it will just skip them in my WHERE cluse. for ex: if there is no @AdmissionNumber (NULL) it will just give whole of the admission numbers there are. is that ok how I wrote it?

ALTER Procedure PJ

(@ImputDate datetime = NULL,
 @AdmissionNumber nvarchar(20) = NULL)
   .....
   .....
   .....
   .....

   where  (@ImputDate is NULL
   or con.entry_date >= @ImputDate)

   and (@Admissionnumber is NULL 
   or em.Admission_No = @AdmissionNumber)

CodePudding user response:

Can you try

WHERE con.entry_date >= (CASE WHEN @ImputDate is null THEN con.entry_date ELSE @ImputDate END)

AND em.Admission_No = (CASE WHEN @AdmissionNumber is null THEN em.Admission_No ELSE @AdmissionNumberEND)
  • Related