Many ELSE IF's

Question

Post reply

Many ELSE IF's

luan.wp

Old Hand

Points: 389
More actions
October 28, 2009 at 7:36 am

#212987

Hi,
i'm with procedure very slow, i know that this is returned cause of ELSE IF.
my code is this.. tks.
CREATE PROC PNX_50_LABFILTRO
@PASTA INT,@FILTRO INT,@OP INT,@EORG INT=NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @dt DATETIME
SET @dt=DATEADD(DAY,-5,CONVERT(DATETIME,CONVERT(VARCHAR,GETDATE(),1)))
--COLETA
IF @FILTRO=1 AND @PASTA=0 AND @OP=0
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'UNA' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K
WHERE L.DCL IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2
ELSE IF @FILTRO=1 AND @PASTA=0
SELECT DISTINCT P.PRC VAR,D.DESCRICAO,'PRC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,FATURAMENTO_CONTAS K,PACIENTES P,PACIENTES_PROCEDENCIAS D
WHERE L.DCL IS NULL AND R.CHV=L.CHV AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.PRC=D.PRC ORDER BY D.DESCRICAO
ELSE IF @FILTRO=2 AND @PASTA=0
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'EXC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E
WHERE L.DCL IS NULL AND R.CHV=L.CHV AND R.EXC=E.EORG AND L.ST = 1 ORDER BY E.DESCRICAO
ELSE IF @FILTRO=3 AND @PASTA=0 AND @OP=1
SELECT DISTINCT P.PRC VAR,D.DESCRICAO,'PRC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,FATURAMENTO_CONTAS K,PACIENTES P,PACIENTES_PROCEDENCIAS D
WHERE L.DCL IS NULL AND R.CHV=L.CHV AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.PRC=D.PRC ORDER BY D.DESCRICAO
ELSE IF @FILTRO=3 AND @PASTA=0
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'UNA' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K
WHERE L.DCL IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2
ELSE IF @FILTRO=4 AND @PASTA=0 AND @OP=0
SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X
WHERE L.DCL IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 AND R.EORG=X.EORG
ELSE IF @FILTRO=4 AND @PASTA=0 AND @OP=1
SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X
WHERE L.DCL IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID=2 AND R.EORG=X.EORG
ELSE IF @FILTRO=4 AND @PASTA=0 AND @OP=2
SELECT DISTINCT R.EORG VAR,E.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E
WHERE L.DCL IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG
ELSE IF @FILTRO=5 AND @PASTA=0 AND @OP=0
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM KIT_SERVICOS K,EORG E,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I
WHERE K.EORG=E.EORG AND K.ST=1 AND K.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.ID NOT IN(3,4)
ELSE IF @FILTRO=5 AND @PASTA=0 AND @OP=1
SELECT PRC VAR,DESCRICAO,'PRC' TIP FROM PACIENTES_PROCEDENCIAS WHERE ST=1
ELSE IF @FILTRO=5 AND @PASTA=0 AND @OP=2
SELECT PRC VAR,DESCRICAO,'PRC' TIP FROM PACIENTES_PROCEDENCIAS WHERE ST=1
UNION
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM KIT_SERVICOS K,EORG E,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I
WHERE K.EORG=E.EORG AND K.ST=1 AND K.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.ID NOT IN(3,4)
ELSE IF @FILTRO=6 AND @PASTA=0
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM EORG E
WHERE EXISTS(SELECT 1 FROM PACIENTES_REQUISICOES R,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I WHERE R.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.EORG=R.EORG AND R.DT>=@DT)
-- SEPARAÇÃO
ELSE IF @PASTA=1 AND @FILTRO=1 AND @OP=1
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'UNA' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K
WHERE L.DCL IS NOT NULL AND L.DT_MAPA IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2
ELSE IF @PASTA=1 AND @FILTRO=1
SELECT DISTINCT P.PRC VAR,D.DESCRICAO,'PRC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,FATURAMENTO_CONTAS K,PACIENTES P,PACIENTES_PROCEDENCIAS D
WHERE L.DCL IS NOT NULL AND L.DT_MAPA IS NULL AND R.CHV=L.CHV AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.PRC=D.PRC
ELSE IF @PASTA=1 AND @FILTRO<>4 AND @FILTRO<>5 AND @FILTRO<>6
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'EXC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E
WHERE L.DCL IS NOT NULL AND L.DT_MAPA IS NULL AND R.CHV=L.CHV AND R.EXC=E.EORG AND L.ST = 1
ELSE IF @PASTA=1 AND @FILTRO=4 AND @OP=0
SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X
WHERE L.DCL IS NOT NULL AND L.DT_MAPA IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 AND R.EORG=X.EORG
ELSE IF @PASTA=1 AND @FILTRO=4 AND @OP=1
SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X
WHERE L.DCL IS NOT NULL AND L.DT_MAPA IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID=2 AND R.EORG=X.EORG
ELSE IF @PASTA=1 AND @FILTRO=4 AND @OP=2
SELECT DISTINCT R.EORG VAR,E.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E
WHERE L.DCL IS NOT NULL AND L.DT_MAPA IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG
ELSE IF @FILTRO=5 AND @PASTA=1
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'EXC' TIP
FROM EORG_SERVICOS K,EORG E,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I
WHERE K.EORG=E.EORG AND K.ST=1 AND K.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.ESUP=@EORG
ELSE IF @FILTRO=6 AND @PASTA=1
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM EORG E
WHERE EXISTS(SELECT 1 FROM PACIENTES_REQUISICOES R,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I WHERE R.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.EORG=R.EORG AND R.DT>=@DT)
--EXECUÇÃO
ELSE IF @PASTA=2 AND @FILTRO=1 AND @OP=1
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'UNA' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K
WHERE L.DT_MAPA IS NOT NULL AND L.DRS IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2
ELSE IF @PASTA=2 AND @FILTRO=1
SELECT DISTINCT P.PRC VAR,D.DESCRICAO,'PRC' TIP
FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,FATURAMENTO_CONTAS K,PACIENTES P left join PACIENTES_PROCEDENCIAS D on P.PRC=D.PRC
WHERE L.DT_MAPA IS NOT NULL AND L.DRS IS NULL AND R.CHV=L.CHV AND R.NCT=K.NCT AND P.PAC=K.PAC
ELSE IF @PASTA=2 AND @FILTRO<>4 AND @FILTRO<>5 AND @FILTRO<>6
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'EXC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E
WHERE L.DT_MAPA IS NOT NULL AND L.DRS IS NULL AND R.CHV=L.CHV AND R.EXC=E.EORG AND L.ST = 1
ELSE IF @PASTA=2 AND @FILTRO=4 AND @OP=0
SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X
WHERE L.DT_MAPA IS NOT NULL AND L.DRS IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 AND R.EORG=X.EORG
ELSE IF @PASTA=2 AND @FILTRO=4 AND @OP=1
SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X
WHERE L.DT_MAPA IS NOT NULL AND L.DRS IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID=2 AND R.EORG=X.EORG
ELSE IF @PASTA=2 AND @FILTRO=4 AND @OP=2
SELECT DISTINCT R.EORG VAR,E.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E
WHERE L.DT_MAPA IS NOT NULL AND L.DRS IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG
ELSE IF @FILTRO=5 AND @PASTA=2
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'EXC' TIP
FROM EORG_SERVICOS K,EORG E,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I
WHERE K.EORG=E.EORG AND K.ST=1 AND K.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.ESUP=@EORG
ELSE IF @FILTRO=6 AND @PASTA=2
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM EORG E
WHERE EXISTS(SELECT 1 FROM PACIENTES_REQUISICOES R,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I WHERE R.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.EORG=R.EORG AND R.DT>=@DT)
-- LIBERAÇÃO
ELSE IF @PASTA=3 AND @FILTRO=1 AND @OP=1
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'UNA' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K
WHERE L.DRS IS NOT NULL AND L.DT_LIB IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2
ELSE IF @PASTA=3 AND @FILTRO=1
SELECT DISTINCT P.PRC VAR,D.DESCRICAO,'PRC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,FATURAMENTO_CONTAS K,PACIENTES P,PACIENTES_PROCEDENCIAS D
WHERE L.DRS IS NOT NULL AND L.DT_LIB IS NULL AND R.CHV=L.CHV AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.PRC=D.PRC
ELSE IF @PASTA=3 AND @FILTRO<>4 AND @FILTRO<>5 AND @FILTRO<>6
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'EXC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E
WHERE L.DRS IS NOT NULL AND L.DT_LIB IS NULL AND R.CHV=L.CHV AND R.EXC=E.EORG AND L.ST = 1
ELSE IF @PASTA=3 AND @FILTRO=4 AND @OP=0
SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X
WHERE L.DRS IS NOT NULL AND L.DT_LIB IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 AND R.EORG=X.EORG
ELSE IF @PASTA=3 AND @FILTRO=4 AND @OP=1
SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X
WHERE L.DRS IS NOT NULL AND L.DT_LIB IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID=2 AND R.EORG=X.EORG
ELSE IF @PASTA=3 AND @FILTRO=4 AND @OP=2
SELECT DISTINCT R.EORG VAR,E.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E
WHERE L.DRS IS NOT NULL AND L.DT_LIB IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG
ELSE IF @FILTRO=5 AND @PASTA=3
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'EXC' TIP
FROM EORG_SERVICOS K,EORG E,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I
WHERE K.EORG=E.EORG AND K.ST=1 AND K.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.ESUP=@EORG
ELSE IF @FILTRO=6 AND @PASTA=3
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM EORG E
WHERE EXISTS(SELECT 1 FROM PACIENTES_REQUISICOES R,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I WHERE R.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.EORG=R.EORG AND R.DT>=@DT)
-- ENTREGA
ELSE IF @PASTA=4 AND @FILTRO=1 AND @OP=1
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'UNA' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K
WHERE L.DT_LIB IS NOT NULL AND L.DT_IPR IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 AND l.DCL>=DATEADD(MONTH,-2,GETDATE())
ELSE IF @PASTA=4 AND @FILTRO=1
SELECT DISTINCT P.PRC VAR,D.DESCRICAO,'PRC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,FATURAMENTO_CONTAS K,PACIENTES P,PACIENTES_PROCEDENCIAS D
WHERE L.DT_LIB IS NOT NULL AND DT_IPR IS NULL AND R.CHV=L.CHV AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.PRC=D.PRC AND l.DCL>=DATEADD(MONTH,-2,GETDATE())
ELSE IF @PASTA=4 AND @FILTRO=2
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'EXC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E
WHERE L.DT_LIB IS NOT NULL AND L.DT_IPR IS NULL AND R.CHV=L.CHV AND R.EXC=E.EORG AND L.ST = 1
ELSE IF @PASTA=4 AND @FILTRO=3 AND @OP=1
SELECT DISTINCT P.PRC VAR,D.DESCRICAO,'PRC' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,FATURAMENTO_CONTAS K,PACIENTES P,PACIENTES_PROCEDENCIAS D
WHERE L.DT_LIB IS NOT NULL AND DT_IPR IS NULL AND R.CHV=L.CHV AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.PRC=D.PRC AND l.DCL>=DATEADD(MONTH,-2,GETDATE())
ELSE IF @PASTA=4 AND @FILTRO<>4 AND @FILTRO<>5 AND @FILTRO<>6
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'UNA' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K
WHERE L.DT_LIB IS NOT NULL AND L.DT_IPR IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 AND l.DCL>=DATEADD(MONTH,-2,GETDATE())
ELSE IF @PASTA=4 AND @FILTRO=4 AND @OP=0
SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X
WHERE L.DT_LIB IS NOT NULL AND L.DT_IPR IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2 AND R.EORG=X.EORG
ELSE IF @PASTA=4 AND @FILTRO=4 AND @OP=1
SELECT DISTINCT X.EORG VAR,X.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K,EORG X
WHERE L.DT_LIB IS NOT NULL AND L.DT_IPR IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID=2 AND R.EORG=X.EORG
ELSE IF @PASTA=4 AND @FILTRO=4 AND @OP=2
SELECT DISTINCT R.EORG VAR,E.DESCRICAO,'USOL' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E
WHERE L.DT_LIB IS NOT NULL AND L.DT_IPR IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG
ELSE IF @FILTRO=5 AND @PASTA=4 AND @OP=0
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM KIT_SERVICOS K,EORG E,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I
WHERE K.EORG=E.EORG AND K.ST=1 AND K.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.ID NOT IN(3,4)
ELSE IF @FILTRO=5 AND @PASTA=4 AND @OP=1
SELECT PRC VAR,DESCRICAO,'PRC' TIP FROM PACIENTES_PROCEDENCIAS WHERE ST=1
ELSE IF @FILTRO=5 AND @PASTA=4 AND @OP=2
SELECT PRC VAR,DESCRICAO,'PRC' TIP FROM PACIENTES_PROCEDENCIAS WHERE ST=1
UNION
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM KIT_SERVICOS K,EORG E,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I
WHERE K.EORG=E.EORG AND K.ST=1 AND K.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.ID NOT IN(3,4)
ELSE IF @FILTRO=6 AND @PASTA=4
SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'USOL' TIP FROM EORG E
WHERE EXISTS(SELECT 1 FROM PACIENTES_REQUISICOES R,PRODUTOS_SERVICOS S,PRODUTOS_SERVICOS_ID I WHERE R.COD=S.COD AND S.ID_PS=I.ID_PS AND I.ID_GRP='L' AND E.EORG=R.EORG AND R.DT>=@DT)

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You might be better off writing separate SP's for each set of conditions and making this a master procedure.

It is WAY too long to try to understand what is happening.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

As Jack Corbett stated:
You might be better off writing separate SP's for each set of conditions and making this a master procedure.

Try this approach

1. For @PASTA pass in the value of 1 or zero

2. For @FILTRO pass in the value of 2 or zero

3. For @OP pass in the value of 4 or zero

4. For @EORG pass in the value of 8 or zero

5. Insure that none of the passed values are NULL

Then

DECLARE @Sum INT

SET @Sum @PASTA + @FILTRO + @OP + @EORG

Then create the this Master procedure as

IF @Sum = 1

EXEC sp_one

ELSE IF @Sum = 2

EXEC sp_two

ELSE IF @Sum = 3

EXEC sp_3

-- continue on using all possible combinations of the sum of the 4 passed parameters. Which is 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12

sp_one would be your code:

SELECT DISTINCT E.EORG VAR,E.DESCRICAO,'UNA' TIP FROM PACIENTES_REQUISICOES R,LAB_RESULTADOS L,EORG E,PACIENTES P,FATURAMENTO_CONTAS K

WHERE L.DCL IS NULL AND R.CHV=L.CHV AND R.EORG=E.EORG AND R.NCT=K.NCT AND P.PAC=K.PAC AND P.LOC=E.EORG AND E.ID<>2

and similarly for the additional sp's. This approach could be easily tested for just a few separate sps to determine how much if any improvement in performance will be gained.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

luan.wp Old Hand Points: 389 More actions · Answer 3

my procedure are fast, but only after execute a first time.