Arranged stored procedure with bifurcations in sql server 2000

Question

Arranged stored procedure with bifurcations in sql server 2000

Navigation

#1 by (0 votes)

0

I'm complicated when it comes to putting together a stored procedure. The query is hardcodeed in php in this way:

if(trim($id) != ''){ 
      $query = "select * from PACIENTES where idpac in (select pac_id from contacto where id = ".$id.") ";

   }else{ 
        $query = "select * from PACIENTES where 1=1 ";
      if(trim($historia_clinica) != ''){
        $query = $query." and historia_clinica_nro = '".$historia_clinica."'";
      }
      if(trim($documento) != ''){
        $query = $query." and numero = '".$documento."'";  
      }
      if(trim($nombre) != ''){
        $query = $query." and nombre like '%".str_replace(' ', '%',$nombre)."%'";  
      }
      if(trim($telefono) != ''){
        $query = $query." and (ntelefo1 = '".$telefono."' or ntelefo2 = '".$telefono."' ) ";
      }
      if(trim($email) != ''){
        //$query =$query." and mail = '".$email."'";
        $query = $query." and hc_nro  in (select idHistoriaClinica from paciente where mail ='".$email."')";
      }
      if(trim($id_paciente) != ''){
        $query =$query." and id_paciente = '".$id_paciente."'";
      }
    }

I was writing something like this:

create sp_getPacientes
@id varchar(3),
@historia_clinica varchar(10),
@documento varchar(10),
@nombre varchar(20),
@telefono varchar(15),
@email varchar(30),
@id_paciente varchar(5)
as
 begin
   if @id is not null 
     select * from PACIENTES where id_paciente in (select paciente_id from 
    contacto where id = @id);

I need logic and an example, I do not ask you to solve everything. Thank you! Greetings

sql stored-procedure

asked by GAL 10.08.2017 в 21:31

source

1 answer

How to start creating pdf with the mpdf library? Fill a text from a select

score 0 · Answer 1

As far as I understand, if you receive the parameter @id , return all the fields of the table (s) in the PATIENTS table whose idpac is in the table CONTACTOS . This is what you already codified in your SP.

In case this parameter is the string null ( '' ), in the ELSE, it arms a SELECT that by default brings all the patients, that's why the where 1 = 1 , this condition is always true .

To this initial condition, add the conditions indicated by the rest of the parameters that are not null.

For example if you receive '@id =' ', @ document =' 1234 ', ... el select' resulting would be:

SELECT * FROM PACIENTES
WHERE 1 = 1
    AND documento = '1234'

that is, only patients with a document equal to '1234'. Similarly with each of the other parameters

This last use of optional parameters can be done without asking if the parameter is '' using OR :

SELECT *
FROM Pacientes
WHERE 1 = 1
    AND (historia_clinica_nro = @historia_clinica OR @historia_clinica = '')
    AND (documento = @documento OR @documento = '')
    AND (<campo_tabla> = @parametro OR @parametro = '')

I hope I have helped you, regards.