Post reply

Problem whit a store procedure

  • November 6, 2008 at 12:01 pm

    #383324

    I have the following procedure:

    create procedure spTotalPrestamosTipoUsuarioDependencias @Dependencia varchar(50) ,@TipoUsuario varchar(50), @FechaIni varchar(10), @FechaFin varchar(10), @Tipo varchar(30)

    as

    if @Tipo = 'Usuarios'

    begin

    select count(*) as TotalPrestamosTipoUsuario

    from dbo.Servicios S, dbo.Usuarios U, dbo.TiposUsuarios T

    where S.IDUsuario = U.IDUsuario

    and U.IDTipoUsuario = T.IDTipoUsuario

    and T.TipoUsuario = @TipoUsuario

    and S.FechaEntrega between convert (datetime,@FechaIni,103) and convert (datetime,@FechaFin,103)

    end

    there is a way that i send a value to the parameter @TipoUsuario and return the count of all the Types of users there are in the table "TipoUsuario"?.

    The table "TipoUsuario" have the following information:

    Estudiantes de pregrado

    Estudiantes de posgrado

    Egresados de pregrado y postgrado

    Profesores e investigadores

    Empleados

    Usuarios externos

    Usuario VIP

    Thank you for any help

  • November 6, 2008 at 12:39 pm

    #895153

    I would suggest a different procedure to get totals for all groups:

    create procedure spTotalPrestamosTipoUsuarioTotal @FechaIni varchar(10), @FechaFin varchar(10), @Tipo varchar(30)

    as

    begin

    select

    count(*) as TotalPrestamosTipoUsuario,

    T.TipoUsuario

    from dbo.Servicios S, dbo.Usuarios U, dbo.TiposUsuarios T

    where S.IDUsuario = U.IDUsuario

    and U.IDTipoUsuario = T.IDTipoUsuario

    and S.FechaEntrega between convert (datetime,@FechaIni,103) and convert (datetime,@FechaFin,103)

    GROUP BY T.TipoUsuario

    end

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply