none
Procedimientos almacenados dinamicos RRS feed

  • Pregunta

  • Buenas, estoy creando una aplicación en vs c# 2015 que filtra datos de la población, la base de datos está en sql server 2016, luego, por ejemplo mi base de datos tiene columnas como: Nombres, apellido1, apellido2, edad, nombre de la madre, etc.

    Necesito hacer las consultas por medio de procedimientos almacenados entonces si por ejemplo ocupo buscar datos de personas de 25 años y que se llamen Juan, pero después necesito buscar datos y utilizo todos los filtros o quiero buscar datos y solo usar algunos filtros.

    Tengo serias dudas de como podría crear un procedimiento dinámico(si es que existen),  que a como me pueda recibir todos los filtros, también me reciba solo algunos. 


    sábado, 11 de marzo de 2017 23:05

Respuestas

  • Hola, una forma de declarar parámetros opcionales en sql es utilizar es igualar a null el parámetro que quieras que sea opcional

    CREATE PROCEDURE usp_lista_empleados ( @Nombre VARCHAR(100), @Apellido1 VARCHAR(100) =NULL

    --....... ) AS SELECT * FROM PERSONAS WHERE Nombre LIKE @Nombre OR Apellido1 LIKE @Apellido1

    --usp_lista_empleados 'JUAN'


    otra cosa que podrías hacer es declarar un procedure y manejar los null en el filtro del WHERE con un OR

    CREATE PROCEDURE usp_lista_empleados ( @Nombre VARCHAR(100), @Apellido1 VARCHAR(100)

    --...... ) AS SELECT * FROM PERSONAS WHERE (Nombre LIKE @Nombre OR @Nombre IS NULL) AND (Apellido1 LIKE @Apellido1 OR @Apellido1 IS NULL)

    y envías un valor de null desde el aplicativo  en el caso que quieras que el filtro siempre devuelva un valor lógico de true, así podrías enviar los parámetros acá referencia

    SqlCommand cmd = new SqlCommand("usp_lista_empleados", conn);
               cmd.CommandType = CommandType.StoredProcedure;	
               cmd.Parameters.AddWithValue("@Nombre"    ,Nombre==sting.Empty? (object)System.DBNull.Value : Nombre);
    	   cmd.Parameters.AddWithValue("@Apellido1" ,Apellido1 ==string.Empty? (object)System.DBNull.Value : Apellido1);
               .......

    Si la respuesta te fue útil vótala como tal,y si fue respuesta márcala. Solo dejo en el foro mis post si fueron útiles , de lo contrario y por mantener el orden los borro. Saludos. Lima-Perú.

    domingo, 12 de marzo de 2017 0:01

Todas las respuestas

  • Hola, una forma de declarar parámetros opcionales en sql es utilizar es igualar a null el parámetro que quieras que sea opcional

    CREATE PROCEDURE usp_lista_empleados ( @Nombre VARCHAR(100), @Apellido1 VARCHAR(100) =NULL

    --....... ) AS SELECT * FROM PERSONAS WHERE Nombre LIKE @Nombre OR Apellido1 LIKE @Apellido1

    --usp_lista_empleados 'JUAN'


    otra cosa que podrías hacer es declarar un procedure y manejar los null en el filtro del WHERE con un OR

    CREATE PROCEDURE usp_lista_empleados ( @Nombre VARCHAR(100), @Apellido1 VARCHAR(100)

    --...... ) AS SELECT * FROM PERSONAS WHERE (Nombre LIKE @Nombre OR @Nombre IS NULL) AND (Apellido1 LIKE @Apellido1 OR @Apellido1 IS NULL)

    y envías un valor de null desde el aplicativo  en el caso que quieras que el filtro siempre devuelva un valor lógico de true, así podrías enviar los parámetros acá referencia

    SqlCommand cmd = new SqlCommand("usp_lista_empleados", conn);
               cmd.CommandType = CommandType.StoredProcedure;	
               cmd.Parameters.AddWithValue("@Nombre"    ,Nombre==sting.Empty? (object)System.DBNull.Value : Nombre);
    	   cmd.Parameters.AddWithValue("@Apellido1" ,Apellido1 ==string.Empty? (object)System.DBNull.Value : Apellido1);
               .......

    Si la respuesta te fue útil vótala como tal,y si fue respuesta márcala. Solo dejo en el foro mis post si fueron útiles , de lo contrario y por mantener el orden los borro. Saludos. Lima-Perú.

    domingo, 12 de marzo de 2017 0:01
  • Hola

    Aunque muchos no recomiendan su uso puedes ejecutar sentencias dinámicas en el procedure tomando algunas medidas de precaución, pero eso es otro tema, este ejemplo es muy elemental. Como quieres que los filtros sean variables puedes al SP pasarle una cadena con los filtros, así desde el "front" puedes "armar" el o los filtros. Usando el código de Augusto

    CREATE PROCEDURE usp_lista_empleados
    (
    @filtro VARCHAR(1000) = ''
    )
    AS
    DECLARE @sql nvarchar(1000),

    SET @sql = 'SELECT  * FROM dbo.Employees WHERE 1=1 ' + @filtro
    EXEC sp_executesql @sql

     

    domingo, 12 de marzo de 2017 0:43
  • Carlos,

    Este articulo excelente, escrito por Erland Sommarskog, explica detalladamente los diferentes metodos que se pueden usar para alcanzar lo que deseas.

    http://www.sommarskog.se/dyn-search-2008.html

    La clave es saber el tipo de data de cada columna, su nullabilidad, indices existentes, frecuencia de llamada que se hara para la busqueda, etc.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    lunes, 13 de marzo de 2017 12:21
  • Hola, una forma de declarar parámetros opcionales en sql es utilizar es igualar a null el parámetro que quieras que sea opcional

    CREATE PROCEDURE usp_lista_empleados ( @Nombre VARCHAR(100), @Apellido1 VARCHAR(100) =NULL

    --....... ) AS SELECT * FROM PERSONAS WHERE Nombre LIKE @Nombre OR Apellido1 LIKE @Apellido1

    --usp_lista_empleados 'JUAN'


    otra cosa que podrías hacer es declarar un procedure y manejar los null en el filtro del WHERE con un OR

    CREATE PROCEDURE usp_lista_empleados ( @Nombre VARCHAR(100), @Apellido1 VARCHAR(100)

    --...... ) AS SELECT * FROM PERSONAS WHERE (Nombre LIKE @Nombre OR @Nombre IS NULL) AND (Apellido1 LIKE @Apellido1 OR @Apellido1 IS NULL)

    y envías un valor de null desde el aplicativo  en el caso que quieras que el filtro siempre devuelva un valor lógico de true, así podrías enviar los parámetros acá referencia

    SqlCommand cmd = new SqlCommand("usp_lista_empleados", conn);
               cmd.CommandType = CommandType.StoredProcedure;	
               cmd.Parameters.AddWithValue("@Nombre"    ,Nombre==sting.Empty? (object)System.DBNull.Value : Nombre);
    	   cmd.Parameters.AddWithValue("@Apellido1" ,Apellido1 ==string.Empty? (object)System.DBNull.Value : Apellido1);
               .......

    Si la respuesta te fue útil vótala como tal,y si fue respuesta márcala. Solo dejo en el foro mis post si fueron útiles , de lo contrario y por mantener el orden los borro. Saludos. Lima-Perú.

    Hola, gracias por responderme.

    Me parece que su respuesta está bastante bien elaborada y se nota que respondes con buena fe y le estoy muy agradecido.

    Ya intente las 2 maneras que me dices y en un principio noto que por ejemplo si busco personas con nombre; Juan , apellido1 = mora , apellido2: Castro, edad: 20 y asi sucesivamente, el procedimiento almacenado me trae muchos registros, por lo que noto que me trae todas las personas con nombre juan, todas personas con apellido1: mora y asi el resto. Puede que no me explicara bien o puede que algo hice mal, pero necesito que mi aplicación traiga las personas que tengan todas esas caracteristicas. es Decir todos los juan mora castro de 20 años.... 

    Actualmente implemente la primera manera que me dijiste, pero no me está funcionando como me gustaria.


    viernes, 17 de marzo de 2017 23:31
  • Hola, podrías postear como lo vienes haciendo y los resultados esperados,

    IF OBJECT_ID('tempdb.dbo.#empleados') IS NOT NULL DROP TABLE #empleados; create table #empleados (id int, nombre varchar(100), apellido1 varchar(100), apellido2 varchar(100), edad int ) go insert into #empleados(id,nombre,apellido1,apellido2,edad) values(1,'JUAN','LOPEZ','VELA',34) insert into #empleados(id,nombre,apellido1,apellido2,edad) values(2,'JOSE','MARTI','LUQUE',36) insert into #empleados(id,nombre,apellido1,apellido2,edad) values(3,'MARIO','POZO','MARCELO',25) insert into #empleados(id,nombre,apellido1,apellido2,edad) values(4,'PABLO','REYEZ','ASTRADA',23) insert into #empleados(id,nombre,apellido1,apellido2,edad) values(5,'JUAN','VERGARA','GALLARDO',45) insert into #empleados(id,nombre,apellido1,apellido2,edad) values(6,'JUAN','GARCIA','CASTRO',22) insert into #empleados(id,nombre,apellido1,apellido2,edad) values(7,'PABLO','ARTEAGA','REY',28) GO IF (OBJECT_ID('usp_lista_empleados_prueba') IS NOT NULL) DROP PROCEDURE usp_lista_empleados_prueba GO CREATE PROCEDURE usp_lista_empleados_prueba ( @nombre varchar(100), @apellido1 varchar(100), @apellido2 varchar(100), @edad int ) AS SELECT * FROM #empleados WHERE (Nombre LIKE @Nombre OR @Nombre IS NULL) AND (Apellido1 LIKE @Apellido1 OR @Apellido1 IS NULL) AND (Apellido2 LIKE @Apellido1 OR @Apellido2 IS NULL) AND (edad LIKE @edad OR @edad IS NULL) GO usp_lista_empleados_prueba 'juan',NULL,NULL,NULL GO usp_lista_empleados_prueba 'juan',NULL,NULL,34 GO usp_lista_empleados_prueba 'juan','garcia',NULL,NULL GO

    DROP TABLE #empleados



    Si la respuesta te fue útil vótala como tal,y si fue respuesta márcala. Solo dejo en el foro mis post si fueron útiles , de lo contrario y por mantener el orden los borro. Saludos. Lima-Perú.


    sábado, 18 de marzo de 2017 0:21
  • Me ha resultado muy útil. Muchas gracias.
    viernes, 21 de febrero de 2020 19:22