none
Ayuda con If o Case en Procedure RRS feed

  • Pregunta

  • Buenas tardes, si alguien puede ayudarme muy agradecido. Tengo el siguiente procedure:USE [macsdm]
    GO
    /****** Object:  StoredProcedure [dbo].[stp_Reporte_Facturado_BLs]    Script Date: 10/29/2016 5:41:56 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================

    -- Create date: 2016-06-27
    -- Description:    Pivot Table Para reporte de que se facturo a Bls
    -- =============================================
    ALTER PROCEDURE [dbo].[stp_Reporte_Facturado_BLs]
        --@FechaDesde varchar(max), @FechaHasta varchar(max), @CodManifiesto int
        @CodManifiesto VARCHAR(max), @CodBl VARCHAR(max)
        --@EstadoFactura varchar(max)
        
    AS
    BEGIN
        DECLARE @cols AS varchar(MAX),@query AS varchar(MAX),
        @EstadoFactura as varchar(max);
        SET @EstadoFactura = 'V';

        SET @cols = STUFF(
                            (
                                SELECT DISTINCT '],[' + CAST(CodigoTipoCargoLocal AS varchar(3))
                                FROM Ct_C_CargosLocalesTipos
                                FOR XML PATH('')
                            )
                            ,1,2,''
                        )+']'


        SET @query = '
        SELECT CodManifiesto, CodBL, NombreTransporte NumViaje, NumBL, IDEquipo ,
            '+@cols+' FROM (
            SELECT b.CodManifiesto, b.CodBL, NombreTransporte, NumViaje, NumBL , IDEquipo,  
            NumPDF, f.Total, TipoCargoLocal
            FROM M_BLs b
            INNER JOIN M_Manifiestos man on b.CodManifiesto = man.CodManifiesto
            INNER JOIN M_CargosLocales mc on b.CodBL = mc.CodBL
            LEFT JOIN V_EquiposBL veq on mc.CodEquipo = veq.CodEquipo
            LEFT JOIN Ct_FacturasDetalles fd on mc.CodCargoLocal = fd.CodCargoLocal
            LEFT JOIN Ct_Facturas f on f.CodFactura = fd.CodFactura
            LEFT JOIN Ct_RecibosDetalles rd on fd.CodDetalleFactura = rd.CodDetalleFactura
            LEFT JOIN Ct_Recibos r on r.CodRecibo = rd.CodRecibo
            WHERE b.codmanifiesto = '+@CodManifiesto+' AND NumBL = '''+@CodBl+'''
            

        ) AS TP

        PIVOT
            (
                MAX(NumPDF)
                for TipoCargoLocal IN ('+@cols+')
            )ptable
    '
        
        --GROUP BY NumBL, '+@cols+',NombreTransporte, NumViaje'
        execute(@query)
    END

    El problema que tengo es que a @CodManifiesto siempre le mandare datos, pero no a @CodBl. Pense que para esto solo era de ponerle un If a @CodBl de que se usara si y solo si lleva datos, pero al hacerlo siempre me da error.

    Alguien podria ayudarme diciendome como deberia hacerlo correctamente?

    Gracias.


    • Editado Estuardo L domingo, 30 de octubre de 2016 0:54
    domingo, 30 de octubre de 2016 0:09

Respuestas

  • Estuardo L,

    Entiendo que ejecutas el procedimiento agregando los parámetros a la colección Parameters, ¿verdad?. De ser así, y en caso no requieras enviar un valor para el parámetro '@CodBI' podrías hacer lo siguiente:

    SqlCommand cmd = new SqlCommand("stp_Reporte_Facturado_BLs", cn);
    
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@CodManifiesto", txtCodigoManifiesto.Text);
    if (!string.IsNullOrEmpty(txtCodigoBl.Text))
    	cmd.Parameters.AddWithValue("@CodBl", txtCodigoBl.Text);
    
    cmd.ExecuteNonQuery();

    El procedimiento almacenado tendría la siguiente forma:

    ALTER PROCEDURE [dbo].[stp_Reporte_Facturado_BLs]    
        @CodManifiesto varchar(max),
        @CodBl varchar(max) = NULL
    AS
    BEGIN
        --Instrucciones anteriores
        'WHERE b.codmanifiesto=' + @CodManifiesto + ' AND (NumBL=' + @CodBl + ' OR @CodBl IS NULL)'
        --Instrucciones siguientes
    END


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    domingo, 30 de octubre de 2016 1:09
  • El problema que estas teniendo lo pudieras evitar si en vez de concatenar valores y usar EXECUTE para ejecutar tu query dinamico, uses SP_EXECUTESQL y parametrizes la sentencia.

    '... WHERE b.codmanifiesto = @CodManifiesto AND ( NumBL= @CodBl OR @CodBl IS NULL )'

    EXECUTE sys.sp_executesql @query, N'@CodManifiesto varchar(max), @CodBI varchar(max)', @CodManifiesto, @CodBI;

    De esta forma no tienes que lidear con los apostrofes requeridos cuando concatenas los parametros con la cadena, ni convertir parametros de otros tipos hacia cadena. Por ultimo pero mas importante, te cubres de que se pueda inyectar codigo SQL mediante los parametros y su concatenacion.

    Tambien te sugiero que uses tipos de dato mas apropiados para tus parametros. No comprendo por que todos tus parametros son VARCHAR(MAX) , incluyendo los llamados @Fechaxxxx.


    AMB

    Some guidelines for posting questions...

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

    lunes, 31 de octubre de 2016 13:05

Todas las respuestas

  • Estuardo L,

    Entiendo que ejecutas el procedimiento agregando los parámetros a la colección Parameters, ¿verdad?. De ser así, y en caso no requieras enviar un valor para el parámetro '@CodBI' podrías hacer lo siguiente:

    SqlCommand cmd = new SqlCommand("stp_Reporte_Facturado_BLs", cn);
    
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@CodManifiesto", txtCodigoManifiesto.Text);
    if (!string.IsNullOrEmpty(txtCodigoBl.Text))
    	cmd.Parameters.AddWithValue("@CodBl", txtCodigoBl.Text);
    
    cmd.ExecuteNonQuery();

    El procedimiento almacenado tendría la siguiente forma:

    ALTER PROCEDURE [dbo].[stp_Reporte_Facturado_BLs]    
        @CodManifiesto varchar(max),
        @CodBl varchar(max) = NULL
    AS
    BEGIN
        --Instrucciones anteriores
        'WHERE b.codmanifiesto=' + @CodManifiesto + ' AND (NumBL=' + @CodBl + ' OR @CodBl IS NULL)'
        --Instrucciones siguientes
    END


    Espero que la información proporcionada te haya sido de utilidad, quedo atento a tus comentarios.
    domingo, 30 de octubre de 2016 1:09
  • Buenas noches, gracias por tu valiosa ayuda. Lo que envio por ejemplo es lo siguiente:

    EXECUTE stp_Reporte_Facturado_BLs "4443" , "ZIMUBGI0090854"

    y Ahora me da el siguiente error:

    Msg 137, Level 15, State 2, Line 14
    Must declare the scalar variable "@Bl".

    El procedimiento como quedó es el siguiente:

    USE [macsdm]
    GO
    /****** Object:  StoredProcedure [dbo].[stp_Reporte_Facturado_BLs]    Script Date: 10/29/2016 9:39:14 PM ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:        Alexis Juarez
    -- Create date: 2016-06-27
    -- Description:    Pivot Table Para reporte de que se facturo a Bls
    -- =============================================
    ALTER PROCEDURE [dbo].[stp_Reporte_Facturado_BLs]
        --@FechaDesde varchar(max), @FechaHasta varchar(max), @CodManifiesto int
        @CodManifiesto VARCHAR(max),
        @Bl varchar(max) = NULL
        --@EstadoFactura varchar(max)
        
    AS
    BEGIN
        DECLARE @cols AS varchar(MAX),@query AS varchar(MAX),
        @EstadoFactura as varchar(max);
        SET @EstadoFactura = 'V';

        SET @cols = STUFF(
                            (
                                SELECT DISTINCT '],[' + CAST(CodigoTipoCargoLocal AS varchar(3))
                                FROM Ct_C_CargosLocalesTipos
                                FOR XML PATH('')
                            )
                            ,1,2,''
                        )+']'


        SET @query = '
        SELECT CodManifiesto, CodBL, NombreTransporte NumViaje, NumBL, IDEquipo ,
            '+@cols+' FROM (
            SELECT b.CodManifiesto, b.CodBL, NombreTransporte, NumViaje, NumBL , IDEquipo,  
            NumPDF, f.Total, TipoCargoLocal
            FROM M_BLs b
            INNER JOIN M_Manifiestos man on b.CodManifiesto = man.CodManifiesto
            INNER JOIN M_CargosLocales mc on b.CodBL = mc.CodBL
            LEFT JOIN V_EquiposBL veq on mc.CodEquipo = veq.CodEquipo
            LEFT JOIN Ct_FacturasDetalles fd on mc.CodCargoLocal = fd.CodCargoLocal
            LEFT JOIN Ct_Facturas f on f.CodFactura = fd.CodFactura
            LEFT JOIN Ct_RecibosDetalles rd on fd.CodDetalleFactura = rd.CodDetalleFactura
            LEFT JOIN Ct_Recibos r on r.CodRecibo = rd.CodRecibo
            WHERE b.codmanifiesto = '+@CodManifiesto+' AND (NumBL=''' + @Bl + ''' OR @Bl IS NULL )
            
            
            
            

        ) AS TP

        PIVOT
            (
                MAX(NumPDF)
                for TipoCargoLocal IN ('+@cols+')
            )ptable
    '
        
        --GROUP BY NumBL, '+@cols+',NombreTransporte, NumViaje'
        execute(@query)
    END

    Despues hice el siguiente cambio:

    WHERE b.codmanifiesto = '+@CodManifiesto+' AND (NumBL= '''+ @Bl +''' OR '''+@Bl+''' IS NULL )

    y ahi funciona pero mandando los dos parametros y si me devuelve resultados.

    pero si mando solo uno pasa esto:

    Command(s) completed successfully.

    sin mostrarme ningun resultado.



    • Editado Estuardo L domingo, 30 de octubre de 2016 4:25 cambios
    domingo, 30 de octubre de 2016 3:50
  • Corrije esta  expresion  (NumBL= '''+ @Bl +''' OR '''+@Bl+''' IS NULL )   así:   (@Bl IS NULL OR NumBL= '''+ @Bl +'''  )
    domingo, 30 de octubre de 2016 7:46
  • El problema que estas teniendo lo pudieras evitar si en vez de concatenar valores y usar EXECUTE para ejecutar tu query dinamico, uses SP_EXECUTESQL y parametrizes la sentencia.

    '... WHERE b.codmanifiesto = @CodManifiesto AND ( NumBL= @CodBl OR @CodBl IS NULL )'

    EXECUTE sys.sp_executesql @query, N'@CodManifiesto varchar(max), @CodBI varchar(max)', @CodManifiesto, @CodBI;

    De esta forma no tienes que lidear con los apostrofes requeridos cuando concatenas los parametros con la cadena, ni convertir parametros de otros tipos hacia cadena. Por ultimo pero mas importante, te cubres de que se pueda inyectar codigo SQL mediante los parametros y su concatenacion.

    Tambien te sugiero que uses tipos de dato mas apropiados para tus parametros. No comprendo por que todos tus parametros son VARCHAR(MAX) , incluyendo los llamados @Fechaxxxx.


    AMB

    Some guidelines for posting questions...

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

    lunes, 31 de octubre de 2016 13:05