CODEWITHSUNDEEP
sql-serverstored-proceduressql-server-2012parameter-passing
user9768237
user9768237

Reputation:

Stored procedure with optional parameter

I have this procedure, I want to be able to send or not the @sucursal parameter

For example: if I run this, I should get all with values with parameter 9

EXEC Fichada_por_sucursal '2018-05-01','2018-05-05','9'

and if run this, I should get all the values no matter what 

EXEC Fichada_por_sucursal '2018-05-01','2018-05-05',''

Stored procedure:

ALTER PROCEDURE Fichada_por_sucursal 
    (@fecha1 DATE,
     @fecha2 DATE,
     @sucursal INT) 
AS
    SELECT
        CASE DATENAME(dw, a.fecha )
           WHEN 'Monday' then 'Lunes'
           WHEN 'Tuesday ' then 'Martes'
           WHEN 'Wednesday ' then 'Miércoles'
           WHEN 'Thursday' then 'Jueves'
           WHEN 'Friday' then 'Viernes'
           WHEN 'Saturday' then 'Sábado'
           ELSE 'Domingo' 
        END AS Fechadia,
        a.legajo, c.nombres, e.abrv,
        CONVERT(CHAR(10), a.fecha, 103) AS Fecha,
        f.entrada AS Hora_IN,
        a.hora AS ENTRADA,
        f.salida AS Hora_out,
        b.hora AS SALIDA,
        DATEDIFF(HOUR, a.hora, b.hora) AS Horas_trabajadas,
        c.hor_x_jor Horas_jornada,
        DATEDIFF(HOUR, a.hora, b.hora) - hor_x_jor AS Diferencia
    FROM
        fichadas_in a, fichadas_out b, empleados c,
        sucursales d,Clasificacion e, grupo_horario f
    WHERE
        a.Legajo = b.Legajo
        AND a.fecha = b.fecha
        AND a.fecha BETWEEN @fecha1 AND @fecha2
        AND d.codigo = @sucursal
        AND a.legajo = c.legajo
        AND c.CCO = d.Codigo
        AND e.Codigo = c.Clasif
        AND c.grupo_h = f.codigo
    ORDER BY
        a.fecha,legajo

Can I do that?

Upvotes: 1

Views: 8802

Answers (2)

Fred
Fred

Reputation: 5808

Make the parameter nullable (optional) and change and d.codigo=@sucursal to deal with the null:

ALTER PROCEDURE Fichada_por_sucursal (
    @fecha1 date,
    @fecha2 date,
    @sucursal int = NULL) 

    AS

    select CASE DATENAME(dw, a.fecha )
    WHEN 'Monday' then 'Lunes'
    WHEN 'Tuesday ' then 'Martes'
    WHEN 'Wednesday ' then 'Miércoles'
    WHEN 'Thursday' then 'Jueves'
    WHEN 'Friday' then 'Viernes'
    WHEN 'Saturday' then 'Sábado'
    ELSE 'Domingo' END
    as Fechadia,
    a.legajo,c.nombres,
    e.abrv,CONVERT (char(10), a.fecha, 103) as Fecha,
    f.entrada as Hora_IN,
    a.hora as ENTRADA,
    f.salida as Hora_out,
    b.hora as SALIDA,
    DATEDIFF(HOUR,a.hora,b.hora) as Horas_trabajadas,
    c.hor_x_jor Horas_jornada,
     DATEDIFF(HOUR,a.hora,b.hora) -hor_x_jor as Diferencia

from fichadas_in a, fichadas_out b, empleados c,
    sucursales d,Clasificacion e, grupo_horario f
    where a.Legajo=b.Legajo
    and a.fecha=b.fecha
    and a.fecha between @fecha1 and @fecha2
    and d.codigo=ISNULL(@sucursal, d.codigo)
    and a.legajo=c.legajo
    and c.CCO=d.Codigo
    and e.Codigo=c.Clasif
    and c.grupo_h=f.codigo
    Order by a.fecha,legajo

Call it:

EXEC Fichada_por_sucursal '2018-05-01','2018-05-05','9' OR EXEC Fichada_por_sucursal '2018-05-01','2018-05-05'

Upvotes: 2

Sean Lange
Sean Lange

Reputation: 33571

Sure this is pretty easy.

ALTER PROCEDURE Fichada_por_sucursal 
(
    @fecha1 date,
    @fecha2 date,
    @sucursal int = 0 --or whatever you want as a default date (it could also be NULL.
)

But you have a couple of other bad habits in your code. First you really should use ANSI-92 style joins. They have been around now for more than 25 years. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins

Also, you should use aliases that mean something. A, B, C are just bad. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3

Upvotes: 1

Related Questions