Problem whit a store procedure

Question

Post reply

Problem whit a store procedure

EIAnd

Right there with Babe

Points: 743
More actions
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

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

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!