Procedimiento almacenado para busqueda

[Killerx_8937]

Hola Foreros.

les cuento que llevo algo de tiempo desarrolando con php y mysql, pero ahora estoy emprezando con php poo y mysqli con procedimientos almacenados todo bien hasta cuando quise hacer una busqueda.

tengo un formulario de busqueda el cual tiene varios campos
Run,Empresa, Area, Horario, grupodeseguridad,nombre, direccion etc..
quiero hacer busqueda por uno o varios de los campos del formulario, anteriormente yo creaba la consulta validando los campos del formulario en blanco y construyrndo la consulta pero ahora con los procedimientos almacenados no se como hacerlo

Código SQL:

CREATE PROCEDURE SpBusUsuario
(
    IN Run VARCHAR(12),
    IN Empresa VARCHAR(12),
    IN Area INT(2),
    IN Nombre VARCHAR(100),
    IN Gps VARCHAR(50),
    IN Region INT(6),
    IN Provincia INT(6),
    IN Comuna INT(6),
    IN TipoHorario INT(10)
)
BEGIN
    SET @consulta =CONCAT('SELECT *
    FROM `Sis_Usuario`
    INNER JOIN Sis_Areas ON Usuario_Area = Areas_Id
    INNER JOIN Sis_Grupo_Seguridad ON Sis_Usuario.GrupoSeguridadCodigo = Sis_Grupo_Seguridad.GrupoSeguridadCodigo
    INNER JOIN Asis_Horario ON UsuarioTipoHorario = Horario_Id
    WHERE `UsuarioRun` = CONVERT(''', Run ,''' using utf8) collate utf8_spanish_ci
    AND `UsuarioEmpresa` = CONVERT(''', Empresa ,''' using utf8) collate utf8_spanish_ci 
    AND `Usuario_Area` = CONVERT(''', Area,''' using utf8) collate utf8_spanish_ci 
    AND `UsuarioNombre` = CONVERT(''', Nombre ,''' using utf8) collate utf8_spanish_ci 
    AND `Sis_Usuario`.`GrupoSeguridadCodigo` =  CONVERT(''',Gps,''' using utf8) collate utf8_spanish_ci 
    AND `UsuarioRegion` = CONVERT(''', Region, ''' using utf8) collate utf8_spanish_ci  
    AND `UsuarioCiudad` = CONVERT(''', Provincia, ''' using utf8) collate utf8_spanish_ci 
    AND `UsuarioComuna` = CONVERT(''', Comuna , ''' using utf8) collate utf8_spanish_ci 
    AND `UsuarioTipoHorario` = CONVERT(''', TipoHorario, ''' using utf8) collate utf8_spanish_ci ');
    PREPARE ejecutar FROM @consulta;
    EXECUTE ejecutar;
END;
 

si llamo al procedimiento almacenado con todos los campos llenos me realiza la busqueda pero al faltar alguno no

Código SQL:

CALL SpBusUsuario('13.899.942-4','76.047.332-4','1','Patricio Carrasco O.','ADMINISTRADOR','13000','13100','13124','1');
 

hay alguna forma de crear el procedimiento y que al ser llamado verifique cuales valores de laviables estan en blanco u no las considere en la consulta.

me explique bien??

[Killerx_8937]

Estimados habra una forma mas eficiente de hacer esto....

Código SQL:

CREATE PROCEDURE SpTest(IN Run VARCHAR(12),IN Empresa VARCHAR(12))
BEGIN
DECLARE condicion VARCHAR(100);
SET condicion = "";
IF Run <> "" THEN
    IF condicion = " " THEN 
        SET condicion = CONCAT(' WHERE UsuarioRun=''', Run,'''');
    ELSE
        SET condicion = CONCAT(' AND  UsuarioRun=''', Run,''''); 
    END IF;    
END IF;
IF Empresa <> "" THEN
    IF condicion = " " THEN 
        SET condicion = CONCAT(' WHERE UsuarioEmpresa=''', Empresa,'''');
    ELSE
        SET condicion = CONCAT(condicion,' AND UsuarioEmpresa=''', Empresa,'''');
    END IF;    
END IF;
SET @consulta = CONCAT("select * from Sis_Usuario ", condicion);
PREPARE stmt FROM @consulta;
EXECUTE stmt;
END;
 

[gnzsoloyo]

No. Es siempre la misma.
De hecho, esa misma forma se suele usar desde el lado de programación, por lo que en ´realidad usar SP para esto es una decisión de diseño.
La cosa es simple: Si no validas lo que contiene cada parámetro, no puedes crear la sentencia con los parámetros que efectivamente debe llevar.

[Killerx_8937]

primero gracias por responder mi duda.

estoy creando el procedimiento se ejecuta bien sin ningun parametro lista todo, probando de a uno cada parametro bien construye la consulta, pero cuando pruebo con varios parametros todo bien hasta el Usuario Nombre

Código SQL:

CALL SpBusUsuario('13.899.942-4','76.047.332-4','1','patricio','','','','','');
 

error 1054 unknown column 'UsuarioNo' in where clause.

no puedo ver el error debe ser alguna comilla del concat pero no lo encuentro ustedes ven que esta mal

Código SQL:

CREATE PROCEDURE SpBusUsuario
(
    IN Run VARCHAR(12),
    IN Empresa VARCHAR(12),
    IN Area INT(2),
    IN Nombre VARCHAR(100),
    IN Gps VARCHAR(50),
    IN Region INT(6),
    IN Provincia INT(6),
    IN Comuna INT(6),
    IN TipoHorario INT(10)
)
BEGIN
DECLARE condicion VARCHAR(100);
 
SET condicion = "";
 
IF Run <> "" THEN
    IF condicion <> "" THEN 
        SET condicion = CONCAT(' AND  UsuarioRun=''', Run,'''');
    ELSE
        SET condicion = CONCAT(' WHERE UsuarioRun=''', Run,'''');
    END IF;    
END IF;
 
IF Empresa <> "" THEN
    IF condicion <> "" THEN 
        SET condicion = CONCAT(condicion,' AND UsuarioEmpresa=''', Empresa,'''');
    ELSE
        SET condicion = CONCAT(' WHERE UsuarioEmpresa=''', Empresa,'''');
    END IF;    
END IF;
 
IF Area <> "" THEN
    IF condicion <> "" THEN 
        SET condicion = CONCAT(condicion,' AND UsuarioArea=''', Area,'''');
    ELSE
        SET condicion = CONCAT(' WHERE UsuarioArea=''', Area,'''');
    END IF;    
END IF;
IF Nombre <> "" THEN
    IF condicion <> "" THEN 
        SET condicion = CONCAT(condicion,' AND UsuarioNombre LIKE CONVERT(''%', Nombre ,'%'' using utf8) collate utf8_spanish_ci ');
    ELSE
        SET condicion = CONCAT(' WHERE UsuarioNombre LIKE CONVERT(''%', Nombre ,'%'' using utf8) collate utf8_spanish_ci ');
    END IF;    
END IF;
IF Gps <> "" THEN
    IF condicion <> "" THEN 
        SET condicion = CONCAT(condicion,' AND Sis_Usuario.GrupoSeguridadCodigo` = ''',Gps,'''');
    ELSE
        SET condicion = CONCAT(' WHERE Sis_Usuario.GrupoSeguridadCodigo =  ''',Gps,''' ');
    END IF;    
END IF;
IF Region <> "" THEN
    IF condicion <> "" THEN 
        SET condicion = CONCAT(condicion,' AND `UsuarioRegion` = CONVERT(''', Region, ''' using utf8) collate utf8_spanish_ci  ');
    ELSE
        SET condicion = CONCAT(' WHERE `UsuarioRegion` = CONVERT(''', Region, ''' using utf8) collate utf8_spanish_ci  ');
    END IF;    
END IF;
IF Provincia <> "" THEN
    IF condicion = " " THEN 
        SET condicion = CONCAT(condicion,' AND `UsuarioCiudad` = CONVERT(''', Provincia, ''' using utf8) collate utf8_spanish_ci');        
    ELSE
        SET condicion = CONCAT(' WHERE `UsuarioCiudad` = CONVERT(''', Provincia, ''' using utf8) collate utf8_spanish_ci');
    END IF;    
END IF;
IF Comuna <> "" THEN
    IF condicion <> "" THEN 
        SET condicion = CONCAT(condicion,' AND `UsuarioComuna` = CONVERT(''', Comuna , ''' using utf8) collate utf8_spanish_ci ');
    ELSE
        SET condicion = CONCAT(' WHERE `UsuarioComuna` = CONVERT(''', Comuna , ''' using utf8) collate utf8_spanish_ci ');
    END IF;    
END IF;
IF TipoHorario <> "" THEN
    IF condicion <> "" THEN 
        SET condicion = CONCAT(condicion,' AND `UsuarioTipoHorario` = CONVERT(''', TipoHorario, ''' using utf8) collate utf8_spanish_ci');
    ELSE
        SET condicion = CONCAT(' WHERE `UsuarioTipoHorario` = CONVERT(''', TipoHorario, ''' using utf8) collate utf8_spanish_ci');
    END IF;    
END IF;
SET @consulta = CONCAT("SELECT * FROM `Sis_Usuario` INNER JOIN Sis_Areas ON UsuarioArea = Areas_Id INNER JOIN Sis_Grupo_Seguridad ON Sis_Usuario.GrupoSeguridadCodigo = Sis_Grupo_Seguridad.GrupoSeguridadCodigo INNER JOIN Asis_Horario ON UsuarioTipoHorario = Horario_Id ", condicion);
PREPARE stmt FROM @consulta;
EXECUTE stmt;
END;
 

[Killerx_8937]

Amigos ya descubri que era la declaracion de condicion era varchar de largo 100 era muy poco lo cambie a 1000 y listo

Código SQL:

CREATE PROCEDURE SpBusUsuario
(
      IN Run VARCHAR(12),
    IN Empresa VARCHAR(12),
    IN Area INT(2),
    IN Nombre VARCHAR(100),
    IN Gps VARCHAR(50),
    IN Region INT(6),
    IN Provincia INT(6),
    IN Comuna INT(6),
    IN TipoHorario INT(10)
)
 
BEGIN
    DECLARE condicion VARCHAR(1000);
    SET condicion ="";
    
    IF Run <> "" THEN
        IF condicion <> "" THEN
        SET condicion = CONCAT(" AND UsuarioRun='",CONVERT(Run USING utf8) COLLATE utf8_spanish_ci ,"' ");
        ELSE
        SET condicion = CONCAT(" WHERE UsuarioRun='",CONVERT(Run USING utf8) COLLATE utf8_spanish_ci ,"' ");
        END IF;
    END IF;
    
    IF Empresa <> "" THEN
        IF condicion <> "" THEN
        SET condicion = CONCAT(condicion, " AND UsuarioEmpresa='",CONVERT(Empresa USING utf8) COLLATE utf8_spanish_ci ,"' ");
        ELSE
        SET condicion = CONCAT(" WHERE UsuarioEmpresa='",CONVERT(Empresa USING utf8) COLLATE utf8_spanish_ci,"' ");
        END IF;
    END IF;
    
    IF Area <> "" THEN
        IF condicion <> "" THEN
        SET condicion = CONCAT(condicion, " AND UsuarioArea='",CONVERT(Area USING utf8) COLLATE utf8_spanish_ci ,"' ");
        ELSE
        SET condicion = CONCAT(" WHERE UsuarioArea='",CONVERT(Area USING utf8) COLLATE utf8_spanish_ci,"' ");
        END IF;
    END IF; 
 
    IF Nombre <> "" THEN
        IF condicion <> "" THEN
        SET condicion = CONCAT(condicion, " AND UsuarioNombre LIKE '%", CONVERT(Nombre USING utf8) COLLATE utf8_spanish_ci ,"%' ");
        ELSE
        SET condicion = CONCAT(" WHERE UsuarioNombre LIKE '%", CONVERT(Nombre USING utf8) COLLATE utf8_spanish_ci ,"%' ");
        END IF;
    END IF; 
 
    IF Gps <> "" THEN
        IF condicion <> "" THEN
        SET condicion = CONCAT(condicion, " AND Sis_Usuario.GrupoSeguridadCodigo='",CONVERT(Gps USING utf8) COLLATE utf8_spanish_ci ,"' ");
        ELSE
        SET condicion = CONCAT(" WHERE Sis_Usuario.GrupoSeguridadCodigo='",CONVERT(Gps USING utf8) COLLATE utf8_spanish_ci,"' ");
        END IF;
    END IF; 
 
    IF Region <> "" THEN
        IF condicion <> "" THEN
        SET condicion = CONCAT(condicion, " AND UsuarioRegion='",CONVERT(Region USING utf8) COLLATE utf8_spanish_ci ,"' ");
        ELSE
        SET condicion = CONCAT(" WHERE UsuarioRegion='",CONVERT(Region USING utf8) COLLATE utf8_spanish_ci,"' ");
        END IF;
    END IF; 
 
    IF Provincia <> "" THEN
        IF condicion <> "" THEN
        SET condicion = CONCAT(condicion, " AND UsuarioCiudad='",CONVERT(Provincia USING utf8) COLLATE utf8_spanish_ci ,"' ");
        ELSE
        SET condicion = CONCAT(" WHERE UsuarioCiudad='",CONVERT(Provincia USING utf8) COLLATE utf8_spanish_ci,"' ");
        END IF;
    END IF; 
 
    IF Comuna <> "" THEN
        IF condicion <> "" THEN
        SET condicion = CONCAT(condicion, " AND UsuarioComuna='",CONVERT(Comuna USING utf8) COLLATE utf8_spanish_ci ,"' ");
        ELSE
        SET condicion = CONCAT(" WHERE UsuarioComuna='",CONVERT(Comuna USING utf8) COLLATE utf8_spanish_ci,"' ");
        END IF;
    END IF;
 
    IF TipoHorario <> "" THEN
        IF condicion <> "" THEN
        SET condicion = CONCAT(condicion, " AND UsuarioTipoHorario='",CONVERT(TipoHorario USING utf8) COLLATE utf8_spanish_ci ,"' ");
        ELSE
        SET condicion = CONCAT(" WHERE UsuarioTipoHorario='",CONVERT(TipoHorario USING utf8) COLLATE utf8_spanish_ci,"' ");
        END IF;
    END IF;
 
SET @consulta = CONCAT("SELECT * FROM Sis_Usuario INNER JOIN Sis_Areas ON UsuarioArea = Areas_Id INNER JOIN Sis_Grupo_Seguridad ON Sis_Usuario.GrupoSeguridadCodigo = Sis_Grupo_Seguridad.GrupoSeguridadCodigo INNER JOIN Asis_Horario ON UsuarioTipoHorario = Horario_Id ", condicion);
PREPARE stmt FROM @consulta;
EXECUTE stmt;
END;