Blog – Fabiano Neves Amorim

O que fazer quando você não gosta do SQL Server e dos desenvolvedores que irão dar manutenção no seu código?

 

Crie uma consulta simples pra eles

DECLARE @Col1  Integer,

        @Col2  Integer,

        @Col3  Integer;

 

SET @Col1 = <Input1>;

SET @Col2 = <Input2>;

SET @Col3 = <Input3>;

 

IF (@Col3 = 0)

BEGIN

  SELECT Tab1.Col1,

         Tab1.Col2,

         Tab1.Col3,

         Tab1.Col7,

         Tab1.Col8,

         Tab1.Col6,

         Tab1.Col9,

         Tab1.Col10,

         CASE WHEN ((Tab2.Col11 IS NOT NULL) AND

                    (Tab3.Col12 = ‘P’) AND

                    (Tab3.Col13 = ‘G’))

 

              THEN CASE WHEN (Tab2.Col11 = ‘LL’)

                        THEN (SELECT CASE WHEN (Tab4.Col15 = ‘Q’)

                                          THEN Tab4.Col16

                                          ELSE Tab4.Col17

                                     END

                         FROM Tab4(NOLOCK)

                         WHERE Tab4.Col1 = Tab1.Col1 AND

                               Tab4.Col2 = Tab1.Col2 AND

                               Tab4.Col3 = Tab1.Col3 AND

                               Tab4.Col4 IN(SELECT Col4

                                               FROM Tab5(NOLOCK)

                                               WHERE Tab5.Col1 = Tab1.Col1 AND

                                                     Tab5.Col5 = Tab1.Col6))

                        ELSE CASE WHEN (Tab2.Col11 = ‘LF’)

                                  THEN (SELECT CASE WHEN (Tab4_Fixos.Col15 = ‘Q’)

                                                    THEN Tab4_Fixos.Col16

                                                    ELSE Tab4_Fixos.Col17

                                                END

                                        FROM Tab4_Fixos(NOLOCK)

                                        WHERE Tab4_Fixos.Col1 = Tab1.Col1 AND

                                              Tab4_Fixos.Col2 = Tab1.Col2 AND

                                              Tab4_Fixos.Col3 = Tab1.Col3 AND

                                              Tab4_Fixos.Col4 IN(SELECT Col4

                                                                    FROM Tab5(NOLOCK)

                                                                    WHERE Tab5.Col1 = Tab1.Col1 AND

                                                                          Tab5.Col5 = Tab1.Col6))

                                  ELSE CASE WHEN (Tab2.Col11 = ‘2F’)

                                       THEN (SELECT CASE WHEN (Tab6.Col15 = ‘Q’)

                                                         THEN Tab6.Col16

                                                         ELSE Tab6.Col17

                                                     END

                                             FROM Tab6(NOLOCK)

                                             WHERE Tab6.Col1 = Tab1.Col1 AND

                                                   Tab6.Col2 = Tab1.Col2 AND

                                                   Tab6.Col3 = Tab1.Col3 AND

                                                   Tab6.Col4 IN(SELECT Col4

                                                                   FROM Tab5(NOLOCK)

                                                                   WHERE Tab5.Col1 = Tab1.Col1 AND

                                                                         Tab5.Col5 = Tab1.Col6))

                                       ELSE (SELECT CASE WHEN (Tab4_Lim.Col15 = ‘Q’)

                                                         THEN Tab4_Lim.Col16

                                                         ELSE Tab4_Lim.Col17

                                                     END

                                             FROM Tab4_Lim(NOLOCK)

                                             WHERE Tab4_Lim.Col1 = Tab1.Col1 AND

                                                   Tab4_Lim.Col2 = Tab1.Col2 AND

                                                   Tab4_Lim.Col3 = Tab1.Col3 AND

                                                   Tab4_Lim.Col4 IN(SELECT Col4

                                                                       FROM Tab5(NOLOCK)

                                                                       WHERE Tab5.Col1 = Tab1.Col1 AND

                                                                             Tab5.Col5 = Tab1.Col6))

                                       END

                             END

                   END

              ELSE Tab1.Col18

         END AS Col19,

         Tab1.Col18,

         Tab1.Col20,

         Tab7.Col21,

         Tab8.Col22,

         Tab3.Col23,

         Tab1.Col24,

         CASE WHEN (Tab2.Col11 = ‘LL’)

              THEN (SELECT Tab4.Col15

                    FROM Tab4(NOLOCK)

                    WHERE Tab4.Col1 = Tab1.Col1 AND

                          Tab4.Col2 = Tab1.Col2 AND

                          Tab4.Col3 = Tab1.Col3 AND

                         Tab4.Col4 IN(SELECT Col4

                                         FROM Tab5(NOLOCK)

                                        WHERE Tab5.Col1 = Tab1.Col1 AND

                                               Tab5.Col5 = Tab1.Col6))

              WHEN (Tab2.Col11 = ‘LI’)

              THEN (SELECT Tab4_Lim.Col15

                    FROM Tab4_Lim(NOLOCK)

                    WHERE Tab4_Lim.Col1 = Tab1.Col1 AND

                          Tab4_Lim.Col2 = Tab1.Col2 AND

                          Tab4_Lim.Col3 = Tab1.Col3 AND

                          Tab4_Lim.Col4 IN(SELECT Col4

                                              FROM Tab5(NOLOCK)

                                              WHERE Tab5.Col1 = Tab1.Col1 AND

                                                    Tab5.Col5 = Tab1.Col6))

              WHEN (Tab2.Col11 = ‘LF’)

              THEN ‘F’

              WHEN (Tab2.Col11 = ‘2F’)

              THEN ‘2’

         END AS Col15,

        Tab1.Col25

  FROM Tab1(NOLOCK)

  INNER JOIN Tab7(NOLOCK)

     ON Tab7.Col26 = Tab1.Col26 AND

           Tab7.Tipo = Tab1.Tipo

  INNER JOIN Tab8(NOLOCK)

     ON Tab8.Col7 = Tab1.Col7

  INNER JOIN Tab3(NOLOCK)

     ON Tab1.Col1 = Tab3.Col1

  RIGHT OUTER JOIN Tab2(NOLOCK)

     ON Tab2.Col1 = Tab1.Col1 AND

           Tab2.Col2 = Tab1.Col2 AND

           Tab2.Col3 = Tab1.Col3 AND

           Tab2.Col4 IN(SELECT Col4

                           FROM Tab5(NOLOCK)

                          WHERE Tab5.Col1 = Tab1.Col1 AND

                                Tab5.Col5 = Tab1.Col6)

  WHERE Tab1.Col1 = @Col1 AND

        Tab1.Col2 = @Col2 AND

        Tab1.Col3 = @Col3 AND

        Tab1.Col10 > 0 AND

        Tab1.Col8 IS NOT NULL