Query or Stored Procedure

There is a query that looks like it should be written as a Stored Procedure.  It is VERY long.  The query declares variables and has a bunch of CASE statements.  Here is just a snippet of the query.  Is there any reason this should stay as a query or should it be moved to a Stored Procedure?

DECLARE @msID nvarchar(100) = NEWID()
DECLARE @qb nvarchar(5),
        @qc1 nvarchar(250),
        @qc2 nvarchar(250),
        @qc3 nvarchar(250),
        @qc4 nvarchar(250),
        @qcp nvarchar(5),
        @qe nvarchar(5),
        @qim nvarchar(5),
        @qnt nvarchar(5),
        @qnr nvarchar(5),
        @qa nvarchar(5),
        @qsch nvarchar(250),
        @qmd nvarchar(250),
        @qma nvarchar(250),
        @qmo nvarchar(250),
        @qmp nvarchar(250),
        @qmn nvarchar(5),
        @qnts nvarchar(250),
        @qschct nvarchar(100)
IF (@STATUS <> 'Closed')
BEGIN
  IF (@AOL = 'TMA'
    OR @AOL = 'TMA-Expired')
  BEGIN
    IF EXISTS (SELECT
        Matters
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters)
    BEGIN
      SELECT
        @qb = QMBDIT,
        @qc1 = QMCONT1,
        @qc2 = QMCONT2,
        @qc3 = QMCONT3,
        @qc4 = QMCONT4,
        @qcp = QMCORPLLCLP,
        @qe = QMEATMA,
        @qim = QMIDITORIDITADM,
        @qnt = QMNVBDT,
        @qnr = QMNVTRST,
        @qa = QMTMA,
        @qsch = QMSCHSPECIALINS,
        @qmd = QMBD,
        @qma = QMEA,
        @qmo = QMEO,
        @qmp = QMEP,
        @qmn = QMILITADMIN,
        @qnts = QMUPDATENOTES,
        @qschct = QMFROMCONTACT
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters
      UPDATE MattersQSCHEDULING
      SET QMTMA =
                 CASE
                   WHEN @AOL =
                     'TMA-Expired' THEN 'N'
                   WHEN @AOL <> 'TMA-Expired' THEN 'Y'
                 END,
          QMBDIT =
                  CASE @qb
                    WHEN 'Y' THEN 'Y'
                    ELSE @QBDIT
                  END,
          QMNVTRST =
                    CASE @qnr
                      WHEN 'Y' THEN 'Y'
                      ELSE @QNVTR
                    END,
          QMIDITORIDITADM =
                           CASE @qim
                             WHEN 'Y' THEN 'Y'
                             ELSE @QIDIT
                           END,
          QMNVBDT =
                   CASE @qnt
                     WHEN 'Y' THEN 'Y'
                     ELSE @QNVBDT

Open in new window

huerita37Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
For starters, tell us how you would use this code, with emphasis on how it would be executed.

Having the code in a SP or function will save it compiled in SQL Server, meaning it can be called again by various code/apps, even by passing values as parameters that can be assigned to the variables in the code, without having to re-type the entire code block.

Also, this isn't the complete code, as @status and @AOL are not defined but are used in the code. and a lot of variables that are declared up top are not used in the SELECT or UPDATE statement.
0
Jacques Bourgeois (James Burger)PresidentCommented:
Unless you are dynamically building a query (such as when the user select fields and conditions), or are stuck with a DBA that prevents you from doing so, a stored procedure is always a better choice, for many reasons, including maintenance (as hinted in Jim answer), performance (compiled SQL is faster) and security.
0
huerita37Author Commented:
You are correct, this is not the complete code.  It is just a snippet of the code since it is so long.  I wanted to give a 'taste' of how long it was.

This code will not be called anywhere else.  It is ran every night.  There are three different tables that are used.  

First, a query not shown, is called to get some data and put into an array MyArray.
Next, the partial query shown at the top of the page, will take the data from MyArray and put the data into variables which are then used in the VERY long query.

If this is something that should be put into a Stored Procedure then I will have questions on that later.  Right now I just want to know if I should keep it how it was written or take the time to move it into a Stored Procedure.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott PletcherSenior DBACommented:
That code's not particularly long at all.

The big issue is it looks code from a cursor, or some other type of row-by-row processing, when it isn't needed.  In the given code, you can do the UPDATE directly, without the SELECT and/or variables, and even without the IF.
0
huerita37Author Commented:
Remember, I was only showing a SNIPPET.  Here is the whole query:  

DECLARE @msID nvarchar(100) = NEWID()
DECLARE @qb nvarchar(5),
        @qc1 nvarchar(250),
        @qc2 nvarchar(250),
        @qc3 nvarchar(250),
        @qc4 nvarchar(250),
        @qcp nvarchar(5),
        @qe nvarchar(5),
        @qim nvarchar(5),
        @qnt nvarchar(5),
        @qnr nvarchar(5),
        @qa nvarchar(5),
        @qsch nvarchar(250),
        @qmd nvarchar(250),
        @qma nvarchar(250),
        @qmo nvarchar(250),
        @qmp nvarchar(250),
        @qmn nvarchar(5),
        @qnts nvarchar(250),
        @qschct nvarchar(100)
IF (@STATUS <> 'Closed')
BEGIN
  IF (@AOL = 'TMA'
    OR @AOL = 'TMA-Expired')
  BEGIN
    IF EXISTS (SELECT
        Matters
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters)
    BEGIN
      SELECT
        @qb = QMBDIT,
        @qc1 = QMCONT1,
        @qc2 = QMCONT2,
        @qc3 = QMCONT3,
        @qc4 = QMCONT4,
        @qcp = QMCORPLLCLP,
        @qe = QMEATMA,
        @qim = QMIDITORIDITADM,
        @qnt = QMNVBDT,
        @qnr = QMNVTRST,
        @qa = QMTMA,
        @qsch = QMSCHSPECIALINS,
        @qmd = QMBD,
        @qma = QMEA,
        @qmo = QMEO,
        @qmp = QMEP,
        @qmn = QMILITADMIN,
        @qnts = QMUPDATENOTES,
        @qschct = QMFROMCONTACT
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters
      UPDATE MattersQSCHEDULING
      SET QMTMA =
                 CASE
                   WHEN @AOL =
                     'TMA-Expired' THEN 'N'
                   WHEN @AOL <> 'TMA-Expired' THEN 'Y'
                 END,
          QMBDIT =
                  CASE @qb
                    WHEN 'Y' THEN 'Y'
                    ELSE @QBDIT
                  END,
          QMNVTRST =
                    CASE @qnr
                      WHEN 'Y' THEN 'Y'
                      ELSE @QNVTR
                    END,
          QMIDITORIDITADM =
                           CASE @qim
                             WHEN 'Y' THEN 'Y'
                             ELSE @QIDIT
                           END,
          QMNVBDT =
                   CASE @qnt
                     WHEN 'Y' THEN 'Y'
                     ELSE @QNVBDT
                   END,
          QMCORPLLCLP =
                       CASE @qcp
                         WHEN 'Y' THEN 'Y'
                         ELSE @QCORPLLCLP
                       END,
          QMEATMA =
                   CASE @qe
                     WHEN 'Y' THEN 'Y'
                     ELSE @QEATMA
                   END,
          QMILITADMIN =
                       CASE @qmn
                         WHEN 'Y' THEN 'Y'
                         ELSE @QMILITADMIN
                       END,
          QMCONT1 = ISNULL(@QCONT1, @qc1),
          QMCONT2 = ISNULL(@QCONT2, @qc2),
          QMCONT3 = ISNULL(@QCONT3, @qc3),
          QMCONT4 = ISNULL(@QCONT4, @qc4),
          QMBD = ISNULL(@QMBD, @qmd),
          QMEA = ISNULL(@QMEA, @qma),
          QMEO = ISNULL(@QMEO, @qmo),
          QMEP = ISNULL(@QMEPT, @qmp),
          QMSCHSPECIALINS = ISNULL(@QSCHSPECIALINST, @qsch),
          QMUPDATENOTES = ISNULL(@QMUPDATENOTE, @qnts),
          QMFROMCONTACT = ISNULL(@CT, @qschct)
      WHERE Matters = @Matters
    END
    ELSE
    BEGIN
      INSERT INTO MattersQSCHEDULING (MattersQSCHEDULING, Matters, QMIDITORIDITADM, QMNVBDT, QMNVTRST, QMTMA, QMBDIT, QMCORPLLCLP,
      QMCONT1, QMCONT2, QMCONT3, QMCONT4, QMBD, QMEA, QMEO, QMEP, QMEATMA, QMILITADMIN, QMSCHSPECIALINS, QMUPDATENOTES, QMFROMCONTACT)
        VALUES (@msID, @Matters, @QIDIT, @QNVBDT, @QNVTR, CASE WHEN @AOL = 'TMA-Expired' THEN 'N' ELSE 'Y' END, @QBDIT, @QCORPLLCLP, @QCONT1, @QCONT2, @QCONT3, @QCONT4, @QMBD, @QMEA, @QMEO, @QMEPT, @QEATMA, @QMILITADMIN, @QSCHSPECIALINST, @QMUPDATENOTE, @CT)
    END
  END
  IF (@AOL = 'IDIT'
    OR @AOL = 'IDIT ADMIN')
  BEGIN
    IF EXISTS (SELECT
        Matters
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters)
    BEGIN
      SELECT
        @qb = QMBDIT,
        @qc1 = QMCONT1,
        @qc2 = QMCONT2,
        @qc3 = QMCONT3,
        @qc4 = QMCONT4,
        @qcp = QMCORPLLCLP,
        @qe = QMEATMA,
        @qim = QMIDITORIDITADM,
        @qnt = QMNVBDT,
        @qnr = QMNVTRST,
        @qa = QMTMA,
        @qsch = QMSCHSPECIALINS,
        @qmd = QMBD,
        @qma = QMEA,
        @qmo = QMEO,
        @qmp = QMEP,
        @qmn = QMILITADMIN,
        @qnts = QMUPDATENOTES,
        @qschct = QMFROMCONTACT
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters
      UPDATE MattersQSCHEDULING
      SET QMIDITORIDITADM = 'Y',
          QMBDIT =
                  CASE @qb
                    WHEN 'Y' THEN 'Y'
                    ELSE @QBDIT
                  END,
          QMNVTRST =
                    CASE @qnr
                      WHEN 'Y' THEN 'Y'
                      ELSE @QNVTR
                    END,
          QMTMA =
                 CASE @qa
                   WHEN 'Y' THEN 'Y'
                   ELSE @QTMA
                 END,
          QMNVBDT =
                   CASE @qnt
                     WHEN 'Y' THEN 'Y'
                     ELSE @QNVBDT
                   END,
          QMCORPLLCLP =
                       CASE @qcp
                         WHEN 'Y' THEN 'Y'
                         ELSE @QCORPLLCLP
                       END,
          QMEATMA =
                   CASE @qe
                     WHEN 'Y' THEN 'Y'
                     ELSE @QEATMA
                   END,
          QMILITADMIN =
                       CASE @qmn
                         WHEN 'Y' THEN 'Y'
                         ELSE @QMILITADMIN
                       END,
          QMCONT1 = ISNULL(@QCONT1, @qc1),
          QMCONT2 = ISNULL(@QCONT2, @qc2),
          QMCONT3 = ISNULL(@QCONT3, @qc3),
          QMCONT4 = ISNULL(@QCONT4, @qc4),
          QMBD = ISNULL(@QMBD, @qmd),
          QMEA = ISNULL(@QMEA, @qma),
          QMEO = ISNULL(@QMEO, @qmo),
          QMEP = ISNULL(@QMEPT, @qmp),
          QMSCHSPECIALINS = ISNULL(@QSCHSPECIALINST, @qsch),
          QMUPDATENOTES = ISNULL(@QMUPDATENOTE, @qnts),
          QMFROMCONTACT = ISNULL(@CT, @qschct)
      WHERE Matters = @Matters
    END
    ELSE
    BEGIN
      INSERT INTO MattersQSCHEDULING (MattersQSCHEDULING, Matters, QMIDITORIDITADM, QMNVBDT, QMNVTRST, QMTMA, QMBDIT, QMCORPLLCLP, QMCONT1, QMCONT2, QMCONT3, QMCONT4, QMBD, QMEA, QMEO, QMEP, QMEATMA, QMILITADMIN, QMSCHSPECIALINS, QMUPDATENOTES, QMFROMCONTACT)
        VALUES (@msID, @Matters, @QIDIT, @QNVBDT, @QNVTR, @QTMA, @QBDIT, @QCORPLLCLP, @QCONT1, @QCONT2, @QCONT3, @QCONT4, @QMBD, @QMEA, @QMEO, @QMEPT, @QEATMA, @QMILITADMIN, @QSCHSPECIALINST, @QMUPDATENOTE, @CT)
    END
  END
  IF (@AOL = 'BDIT')
  BEGIN
    IF EXISTS (SELECT
        Matters
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters)
    BEGIN
      SELECT
        @qb = QMBDIT,
        @qc1 = QMCONT1,
        @qc2 = QMCONT2,
        @qc3 = QMCONT3,
        @qc4 = QMCONT4,
        @qcp = QMCORPLLCLP,
        @qe = QMEATMA,
        @qim = QMIDITORIDITADM,
        @qnt = QMNVBDT,
        @qnr = QMNVTRST,
        @qa = QMTMA,
        @qsch = QMSCHSPECIALINS,
        @qmd = QMBD,
        @qma = QMEA,
        @qmo = QMEO,
        @qmp = QMEP,
        @qmn = QMILITADMIN,
        @qnts = QMUPDATENOTES,
        @qschct = QMFROMCONTACT
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters
      UPDATE MattersQSCHEDULING
      SET QMBDIT = 'Y',
          QMIDITORIDITADM =
                           CASE @qim
                             WHEN 'Y' THEN 'Y'
                             ELSE @QIDIT
                           END,
          QMNVTRST =
                    CASE @qnr
                      WHEN 'Y' THEN 'Y'
                      ELSE @QNVTR
                    END,
          QMTMA =
                 CASE @qa
                   WHEN 'Y' THEN 'Y'
                   ELSE @QTMA
                 END,
          QMNVBDT =
                   CASE @qnt
                     WHEN 'Y' THEN 'Y'
                     ELSE @QNVBDT
                   END,
          QMCORPLLCLP =
                       CASE @qcp
                         WHEN 'Y' THEN 'Y'
                         ELSE @QCORPLLCLP
                       END,
          QMEATMA =
                   CASE @qe
                     WHEN 'Y' THEN 'Y'
                     ELSE @QEATMA
                   END,
          QMILITADMIN =
                       CASE @qmn
                         WHEN 'Y' THEN 'Y'
                         ELSE @QMILITADMIN
                       END,
          QMCONT1 = ISNULL(@QCONT1, @qc1),
          QMCONT2 = ISNULL(@QCONT2, @qc2),
          QMCONT3 = ISNULL(@QCONT3, @qc3),
          QMCONT4 = ISNULL(@QCONT4, @qc4),
          QMBD = ISNULL(@QMBD, @qmd),
          QMEA = ISNULL(@QMEA, @qma),
          QMEO = ISNULL(@QMEO, @qmo),
          QMEP = ISNULL(@QMEPT, @qmp),
          QMSCHSPECIALINS = ISNULL(@QSCHSPECIALINST, @qsch),
          QMUPDATENOTES = ISNULL(@QMUPDATENOTE, @qnts),
          QMFROMCONTACT = ISNULL(@CT, @qschct)
      WHERE Matters = @Matters
    END
    ELSE
    BEGIN
      INSERT INTO MattersQSCHEDULING (MattersQSCHEDULING, Matters, QMIDITORIDITADM, QMNVBDT, QMNVTRST, QMTMA, QMBDIT, QMCORPLLCLP, QMCONT1, QMCONT2, QMCONT3, QMCONT4, QMBD, QMEA, QMEO, QMEP, QMEATMA, QMILITADMIN, QMSCHSPECIALINS, QMUPDATENOTES, QMFROMCONTACT)
        VALUES (@msID, @Matters, @QIDIT, @QNVBDT, @QNVTR, @QTMA, @QBDIT, @QCORPLLCLP, @QCONT1, @QCONT2, @QCONT3, @QCONT4, @QMBD, @QMEA, @QMEO, @QMEPT, @QEATMA, @QMILITADMIN, @QSCHSPECIALINST, @QMUPDATENOTE, @CT)
    END
  END
  IF (@AOL = 'NVBDT')
  BEGIN
    IF
      EXISTS (SELECT
        Matters
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters)
    BEGIN
      SELECT
        @qb = QMBDIT,
        @qc1 = QMCONT1,
        @qc2 = QMCONT2,
        @qc3 = QMCONT3,
        @qc4 = QMCONT4,
        @qcp = QMCORPLLCLP,
        @qe = QMEATMA,
        @qim = QMIDITORIDITADM,
        @qnt = QMNVBDT,
        @qnr = QMNVTRST,
        @qa = QMTMA,
        @qsch = QMSCHSPECIALINS,
        @qmd = QMBD,
        @qma = QMEA,
        @qmo = QMEO,
        @qmp = QMEP,
        @qmn = QMILITADMIN,
        @qnts = QMUPDATENOTES,
        @qschct = QMFROMCONTACT
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters
      UPDATE MattersQSCHEDULING
      SET QMNVBDT = 'Y',
          QMIDITORIDITADM =
                           CASE @qim
                             WHEN 'Y' THEN 'Y'
                             ELSE @QIDIT
                           END,
          QMNVTRST =
                    CASE @qnr
                      WHEN 'Y' THEN 'Y'
                      ELSE @QNVTR
                    END,
          QMTMA =
                 CASE @qa
                   WHEN 'Y' THEN 'Y'
                   ELSE @QTMA
                 END,
          QMBDIT =
                  CASE @qb
                    WHEN 'Y' THEN 'Y'
                    ELSE @QBDIT
                  END,
          QMCORPLLCLP =
                       CASE @qcp
                         WHEN 'Y' THEN 'Y'
                         ELSE @QCORPLLCLP
                       END,
          QMEATMA =
                   CASE @qe
                     WHEN 'Y' THEN 'Y'
                     ELSE @QEATMA
                   END,
          QMILITADMIN =
                       CASE @qmn
                         WHEN 'Y' THEN 'Y'
                         ELSE @QMILITADMIN
                       END,
          QMCONT1 = ISNULL(@QCONT1, @qc1),
          QMCONT2 = ISNULL(@QCONT2, @qc2),
          QMCONT3 = ISNULL(@QCONT3, @qc3),
          QMCONT4 =
          ISNULL(@QCONT4, @qc4),
          QMBD = ISNULL(@QMBD, @qmd),
          QMEA = ISNULL(@QMEA, @qma),
          QMEO = ISNULL(@QMEO, @qmo),
          QMEP = ISNULL(@QMEPT, @qmp),
          QMSCHSPECIALINS = ISNULL(@QSCHSPECIALINST, @qsch),
          QMUPDATENOTES = ISNULL(@QMUPDATENOTE, @qnts),
          QMFROMCONTACT = ISNULL(@CT, @qschct)
      WHERE Matters = @Matters
    END
    ELSE
    BEGIN
      INSERT INTO MattersQSCHEDULING (MattersQSCHEDULING, Matters, QMIDITORIDITADM, QMNVBDT, QMNVTRST, QMTMA, QMBDIT, QMCORPLLCLP, QMCONT1, QMCONT2, QMCONT3, QMCONT4, QMBD, QMEA, QMEO, QMEP, QMEATMA, QMILITADMIN, QMSCHSPECIALINS, QMUPDATENOTES, QMFROMCONTACT)
        VALUES (@msID, @Matters, @QIDIT, @QNVBDT, @QNVTR, @QTMA, @QBDIT, @QCORPLLCLP, @QCONT1, @QCONT2, @QCONT3, @QCONT4, @QMBD, @QMEA, @QMEO, @QMEPT, @QEATMA, @QMILITADMIN, @QSCHSPECIALINST, @QMUPDATENOTE, @CT)
    END
  END
  IF (@AOL = 'NVTRST')
  BEGIN
    IF EXISTS (SELECT
        Matters
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters)
    BEGIN
      SELECT
        @qb = QMBDIT,
        @qc1 = QMCONT1,
        @qc2 = QMCONT2,
        @qc3 = QMCONT3,
        @qc4 = QMCONT4,
        @qcp = QMCORPLLCLP,
        @qe = QMEATMA,
        @qim =
        QMIDITORIDITADM,
        @qnt = QMNVBDT,
        @qnr = QMNVTRST,
        @qa = QMTMA,
        @qsch = QMSCHSPECIALINS,
        @qmd = QMBD,
        @qma = QMEA,
        @qmo = QMEO,
        @qmp = QMEP,
        @qmn = QMILITADMIN,
        @qnts = QMUPDATENOTES,
        @qschct = QMFROMCONTACT
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters
      UPDATE MattersQSCHEDULING
      SET QMNVTRST = 'Y',
          QMIDITORIDITADM =
                           CASE @qim
                             WHEN 'Y' THEN 'Y'
                             ELSE @QIDIT
                           END,
          QMNVBDT =
                   CASE @qnt
                     WHEN 'Y' THEN 'Y'
                     ELSE @QNVBDT
                   END,
          QMTMA =
                 CASE @qa
                   WHEN 'Y' THEN 'Y'
                   ELSE @QTMA
                 END,
          QMBDIT =
                  CASE @qb
                    WHEN 'Y' THEN 'Y'
                    ELSE @QBDIT
                  END,
          QMCORPLLCLP =
                       CASE @qcp
                         WHEN 'Y' THEN 'Y'
                         ELSE @QCORPLLCLP
                       END,
          QMEATMA =
                   CASE @qe
                     WHEN 'Y' THEN 'Y'
                     ELSE @QEATMA
                   END,
          QMILITADMIN =
                       CASE @qmn
                         WHEN 'Y' THEN 'Y'
                         ELSE @QMILITADMIN
                       END,
          QMCONT1 = ISNULL(@QCONT1, @qc1),
          QMCONT2 = ISNULL(@QCONT2, @qc2),
          QMCONT3 = ISNULL(@QCONT3, @qc3),
          QMCONT4 = ISNULL(@QCONT4, @qc4),
          QMBD = ISNULL(@QMBD, @qmd),
          QMEA = ISNULL(@QMEA, @qma),
          QMEO = ISNULL(@QMEO, @qmo),
          QMEP = ISNULL(@QMEPT, @qmp),
          QMSCHSPECIALINS = ISNULL(@QSCHSPECIALINST, @qsch),
          QMUPDATENOTES =
          ISNULL(@QMUPDATENOTE, @qnts),
          QMFROMCONTACT = ISNULL(@CT, @qschct)
      WHERE Matters = @Matters
    END
    ELSE
    BEGIN
      INSERT INTO MattersQSCHEDULING (MattersQSCHEDULING, Matters, QMIDITORIDITADM, QMNVBDT, QMNVTRST, QMTMA, QMBDIT, QMCORPLLCLP, QMCONT1, QMCONT2, QMCONT3, QMCONT4, QMBD, QMEA, QMEO, QMEP, QMEATMA, QMILITADMIN, QMSCHSPECIALINS, QMUPDATENOTES, QMFROMCONTACT)
        VALUES (@msID, @Matters, @QIDIT, @QNVBDT, @QNVTR, @QTMA, @QBDIT, @QCORPLLCLP, @QCONT1, @QCONT2, @QCONT3, @QCONT4, @QMBD, @QMEA, @QMEO, @QMEPT, @QEATMA, @QMILITADMIN, @QSCHSPECIALINST, @QMUPDATENOTE, @CT)
    END
  END
  IF (@AOL IN ('CORP', 'LLC', 'LP'))
  BEGIN
    IF EXISTS (SELECT
        Matters
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters)
    BEGIN
      SELECT
        @qb = QMBDIT,
        @qc1 = QMCONT1,
        @qc2 = QMCONT2,
        @qc3 = QMCONT3,
        @qc4 = QMCONT4,
        @qcp = QMCORPLLCLP,
        @qe = QMEATMA,
        @qim = QMIDITORIDITADM,
        @qnt = QMNVBDT,
        @qnr = QMNVTRST,
        @qa = QMTMA,
        @qsch = QMSCHSPECIALINS,
        @qmd = QMBD,
        @qma = QMEA,
        @qmo = QMEO,
        @qmp = QMEP,
        @qmn = QMILITADMIN,
        @qnts = QMUPDATENOTES,
        @qschct = QMFROMCONTACT
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters
      UPDATE MattersQSCHEDULING
      SET QMCORPLLCLP = 'Y',
          QMIDITORIDITADM =
                           CASE @qim
                             WHEN 'Y' THEN 'Y'
                             ELSE @QIDIT
                           END,
          QMNVBDT =
                   CASE @qnt
                     WHEN 'Y' THEN 'Y'
                     ELSE @QNVBDT
                   END,
          QMNVTRST =
                    CASE @qnr
                      WHEN 'Y' THEN 'Y'
                      ELSE @QNVTR
                    END,
          QMTMA =
                 CASE @qa
                   WHEN 'Y' THEN 'Y'
                   ELSE @QTMA
                 END,
          QMBDIT =
                  CASE @qb
                    WHEN 'Y' THEN 'Y'
                    ELSE @QBDIT
                  END,
          QMEATMA =
                   CASE @qe
                     WHEN 'Y' THEN 'Y'
                     ELSE @QEATMA
                   END,
          QMILITADMIN =
                       CASE @qmn
                         WHEN 'Y' THEN 'Y'
                         ELSE @QMILITADMIN
                       END,
          QMCONT1 = ISNULL(@QCONT1, @qc1),
          QMCONT2 = ISNULL(@QCONT2, @qc2),
          QMCONT3 = ISNULL(@QCONT3, @qc3),
          QMCONT4 = ISNULL(@QCONT4, @qc4),
          QMBD = ISNULL(@QMBD, @qmd),
          QMEA = ISNULL(@QMEA, @qma),
          QMEO = ISNULL(@QMEO, @qmo),
          QMEP = ISNULL(@QMEPT, @qmp),
          QMSCHSPECIALINS = ISNULL(@QSCHSPECIALINST, @qsch),
          QMUPDATENOTES = ISNULL(@QMUPDATENOTE, @qnts),
          QMFROMCONTACT = ISNULL(@CT, @qschct)
      WHERE Matters = @Matters
    END
    ELSE
    BEGIN
      INSERT INTO MattersQSCHEDULING (MattersQSCHEDULING, Matters, QMIDITORIDITADM, QMNVBDT, QMNVTRST, QMTMA, QMBDIT,
      QMCORPLLCLP, QMCONT1, QMCONT2, QMCONT3, QMCONT4, QMBD, QMEA, QMEO, QMEP, QMEATMA, QMILITADMIN, QMSCHSPECIALINS, QMUPDATENOTES, QMFROMCONTACT)
        VALUES (@msID, @Matters, @QIDIT, @QNVBDT, @QNVTR, @QTMA, @QBDIT, @QCORPLLCLP, @QCONT1, @QCONT2, @QCONT3, @QCONT4, @QMBD, @QMEA, @QMEO, @QMEPT, @QEATMA, @QMILITADMIN, @QSCHSPECIALINST, @QMUPDATENOTE, @CT)
    END
  END
  IF (@AOL = 'ILIT ADMIN')
  BEGIN
    IF EXISTS (SELECT
        Matters
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters)
    BEGIN
      SELECT
        @qb = QMBDIT,
        @qc1 = QMCONT1,
        @qc2 = QMCONT2,
        @qc3 = QMCONT3,
        @qc4 = QMCONT4,
        @qcp = QMCORPLLCLP,
        @qe = QMEATMA,
        @qim = QMIDITORIDITADM,
        @qnt = QMNVBDT,
        @qnr = QMNVTRST,
        @qa = QMTMA,
        @qsch = QMSCHSPECIALINS,
        @qmd = QMBD,
        @qma = QMEA,
        @qmo = QMEO,
        @qmp = QMEP,
        @qmn = QMILITADMIN,
        @qnts = QMUPDATENOTES,
        @qschct = QMFROMCONTACT
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters
      UPDATE MattersQSCHEDULING
      SET QMILITADMIN = 'Y',
          QMBDIT =
                  CASE @qb
                    WHEN 'Y' THEN 'Y'
                    ELSE @QBDIT
                  END,
          QMNVTRST =
                    CASE @qnr
                      WHEN 'Y' THEN 'Y'
                      ELSE @QNVTR
                    END,
          QMTMA =
                 CASE @qa
                   WHEN
                     'Y' THEN 'Y'
                   ELSE @QTMA
                 END,
          QMNVBDT =
                   CASE @qnt
                     WHEN 'Y' THEN 'Y'
                     ELSE @QNVBDT
                   END,
          QMCORPLLCLP =
                       CASE @qcp
                         WHEN 'Y' THEN 'Y'
                         ELSE @QCORPLLCLP
                       END,
          QMEATMA =
                   CASE @qe
                     WHEN 'Y' THEN 'Y'
                     ELSE @QEATMA
                   END,
          QMIDITORIDITADM =
                           CASE @qim
                             WHEN 'Y' THEN 'Y'
                             ELSE @QIDIT
                           END,
          QMCONT1 = ISNULL(@QCONT1, @qc1),
          QMCONT2 = ISNULL(@QCONT2, @qc2),
          QMCONT3 = ISNULL(@QCONT3, @qc3),
          QMCONT4 = ISNULL(@QCONT4, @qc4),
          QMBD = ISNULL(@QMBD, @qmd),
          QMEA = ISNULL(@QMEA, @qma),
          QMEO = ISNULL(@QMEO, @qmo),
          QMEP = ISNULL(@QMEPT, @qmp),
          QMSCHSPECIALINS = ISNULL(@QSCHSPECIALINST, @qsch),
          QMUPDATENOTES = ISNULL(@QMUPDATENOTE, @qnts),
          QMFROMCONTACT = ISNULL(@CT, @qschct)
      WHERE Matters = @Matters
    END
    ELSE
    BEGIN
      INSERT INTO MattersQSCHEDULING (MattersQSCHEDULING, Matters, QMIDITORIDITADM, QMNVBDT, QMNVTRST, QMTMA, QMBDIT, QMCORPLLCLP, QMCONT1, QMCONT2, QMCONT3, QMCONT4, QMBD, QMEA, QMEO, QMEP, QMEATMA, QMILITADMIN, QMSCHSPECIALINS, QMUPDATENOTES, QMFROMCONTACT)
        VALUES (@msID, @Matters, @QIDIT, @QNVBDT, @QNVTR, @QTMA, @QBDIT, @QCORPLLCLP, @QCONT1, @QCONT2, @QCONT3, @QCONT4, @QMBD, @QMEA, @QMEO, @QMEPT, @QEATMA, @QMILITADMIN, @QSCHSPECIALINST, @QMUPDATENOTE, @CT)
    END
  END
  IF (@AOL = 'EATMA')
  BEGIN
    IF EXISTS (SELECT
        Matters
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters)
    BEGIN
      SELECT
        @qb = QMBDIT,
        @qc1 = QMCONT1,
        @qc2 = QMCONT2,
        @qc3 = QMCONT3,
        @qc4 = QMCONT4,
        @qcp = QMCORPLLCLP,
        @qe = QMEATMA,
        @qim = QMIDITORIDITADM,
        @qnt = QMNVBDT,
        @qnr = QMNVTRST,
        @qa = QMTMA,
        @qsch = QMSCHSPECIALINS,
        @qmd = QMBD,
        @qma = QMEA,
        @qmo = QMEO,
        @qmp = QMEP,
        @qmn = QMILITADMIN,
        @qnts = QMUPDATENOTES,
        @qschct = QMFROMCONTACT
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters
      UPDATE MattersQSCHEDULING
      SET QMEATMA = 'Y',
          QMBDIT =
                  CASE @qb
                    WHEN 'Y' THEN 'Y'
                    ELSE @QBDIT
                  END,
          QMNVTRST =
                    CASE @qnr
                      WHEN 'Y' THEN 'Y'
                      ELSE @QNVTR
                    END,
          QMTMA =
                 CASE @qa
                   WHEN 'Y' THEN 'Y'
                   ELSE @QTMA
                 END,
          QMNVBDT =
                   CASE @qnt
                     WHEN 'Y' THEN 'Y'
                     ELSE @QNVBDT
                   END,
          QMCORPLLCLP =
                       CASE @qcp
                         WHEN 'Y' THEN 'Y'
                         ELSE @QCORPLLCLP
                       END,
          QMILITADMIN =
                       CASE @qmn
                         WHEN 'Y' THEN 'Y'
                         ELSE @QMILITADMIN
                       END,
          QMIDITORIDITADM =
                           CASE @qim
                             WHEN 'Y' THEN 'Y'
                             ELSE @QIDIT
                           END,
          QMCONT1 = ISNULL(@QCONT1, @qc1),
          QMCONT2 = ISNULL(@QCONT2, @qc2),
          QMCONT3 = ISNULL(@QCONT3, @qc3),
          QMCONT4 = ISNULL(@QCONT4, @qc4),
          QMBD = ISNULL(@QMBD, @qmd),
          QMEA = ISNULL(@QMEA, @qma),
          QMEO = ISNULL(@QMEO, @qmo),
          QMEP = ISNULL(@QMEPT, @qmp),
          QMSCHSPECIALINS = ISNULL(@QSCHSPECIALINST, @qsch),
          QMUPDATENOTES = ISNULL(@QMUPDATENOTE, @qnts),
          QMFROMCONTACT = ISNULL(@CT, @qschct)
      WHERE Matters = @Matters
    END
    ELSE
    BEGIN
      INSERT INTO MattersQSCHEDULING (MattersQSCHEDULING, Matters, QMIDITORIDITADM, QMNVBDT, QMNVTRST, QMTMA, QMBDIT, QMCORPLLCLP, QMCONT1, QMCONT2, QMCONT3, QMCONT4, QMBD, QMEA, QMEO, QMEP, QMEATMA, QMILITADMIN, QMSCHSPECIALINS, QMUPDATENOTES, QMFROMCONTACT)
        VALUES (@msID, @Matters, @QIDIT, @QNVBDT, @QNVTR, @QTMA, @QBDIT, @QCORPLLCLP, @QCONT1, @QCONT2, @QCONT3, @QCONT4, @QMBD, @QMEA, @QMEO, @QMEPT, @QEATMA, @QMILITADMIN, @QSCHSPECIALINST, @QMUPDATENOTE, @CT)
    END
  END
END
ELSE
BEGIN
  IF EXISTS (SELECT
      Matters
    FROM MattersQSCHEDULING
    WHERE Matters =
    @Matters)
  BEGIN
    UPDATE MattersQSCHEDULING
    SET QMBDIT = 'N',
        QMCONT1 = '',
        QMCONT2 = '',
        QMCONT3 = '',
        QMCONT4 = '',
        QMCORPLLCLP = 'N',
        QMEATMA = 'N',
        QMIDITORIDITADM = 'N',
        QMNVBDT = 'N',
        QMNVTRST = 'N',
        QMTMA = 'N',
        QMBD = '',
        QMEA = '',
        QMEO = '',
        QMEP = '',
        QMILITADMIN = 'N',
        QMSCHSPECIALINS = '',
        QMUPDATENOTES = '',
        QMFROMCONTACT = ''
    WHERE Matters = @Matters
  END
  ELSE
  BEGIN
    INSERT INTO MattersQSCHEDULING (MattersQSCHEDULING, Matters, QMIDITORIDITADM, QMNVBDT, QMNVTRST, QMTMA, QMBDIT, QMCORPLLCLP, QMCONT1, QMCONT2, QMCONT3, QMCONT4, QMBD, QMEA, QMEO, QMEP, QMEATMA, QMILITADMIN, QMSCHSPECIALINS, QMUPDATENOTES, QMFROMCONTACT)
      VALUES (@msID, @Matters, 'N', 'N', 'N', 'N', 'N', 'N', '', '', '', '', '', '', '', '', 'N', 'N', '', '', '')
  END
END

Open in new window

0
Jacques Bourgeois (James Burger)PresidentCommented:
No way you leave that in the code. The time it would take to parse and compile, not counting the optimization (although I do not think that the analyzer would decide to optimize such a query), this goes in a stored procedure.

It would also be a lot easier on your nerves is you ever needed to debug it.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
huerita37Author Commented:
That's what I was thinking.  To make things worse, it was all on one line!!
0
Scott PletcherSenior DBACommented:
I'd normalize that code down to a single UPDATE and a single INSERT, with the appropriate CASE conditions for each aol type, something like below.  I believe that makes the code easier to follow, and esp. easier to compare what columns one type sets vs another type.

I only did the first two types because I don't have time to wade thru all of them, esp. as the code doesn't parse without all the variables in use being DECLAREd first.

DECLARE @msID nvarchar(100) = NEWID()
DECLARE @qb nvarchar(5), @qc1 nvarchar(250), @qc2 nvarchar(250), @qc3 nvarchar(250), @qc4 nvarchar(250),
        @qcp nvarchar(5), @qe nvarchar(5), @qim nvarchar(5),  @qnt nvarchar(5), @qnr nvarchar(5),
        @qa nvarchar(5), @qsch nvarchar(250), @qmd nvarchar(250), @qma nvarchar(250), @qmo nvarchar(250),
        @qmp nvarchar(250), @qmn nvarchar(5), @qnts nvarchar(250), @qschct nvarchar(100)
DECLARE @aol_type varchar(6)

SET @aol_type = 
    CASE WHEN (@AOL = 'TMA' OR @AOL = 'TMA-Expired') THEN 'TMA'
         WHEN (@AOL = 'IDIT' OR @AOL = 'IDIT ADMIN') THEN 'IDIT'
         WHEN (@AOL = 'BDIT') THEN 'BDIT'
         WHEN (@AOL = 'NVBDT') THEN 'NVBDT'
         WHEN (@AOL = 'NVTRST') THEN 'NVTRST'
         WHEN (@AOL IN ('CORP', 'LLC', 'LP')) THEN 'CORP'
         WHEN (@AOL = 'ILIT ADMIN') THEN 'ILIT'
         WHEN (@AOL = 'EATMA') THEN 'EATMA'
         END

--IF (@STATUS <> 'Closed')
--BEGIN
    IF EXISTS (SELECT Matters
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters
    )
    BEGIN

      SELECT
        @qb = QMBDIT, @qc1 = QMCONT1, @qc2 = QMCONT2, @qc3 = QMCONT3, @qc4 = QMCONT4,
        @qcp = QMCORPLLCLP, @qe = QMEATMA, @qim = QMIDITORIDITADM, @qnt = QMNVBDT, @qnr = QMNVTRST,
        @qa = QMTMA, @qsch = QMSCHSPECIALINS, @qmd = QMBD, @qma = QMEA, @qmo = QMEO,
        @qmp = QMEP, @qmn = QMILITADMIN, @qnts = QMUPDATENOTES, @qschct = QMFROMCONTACT
      FROM MattersQSCHEDULING
      WHERE Matters = @Matters
      UPDATE MattersQSCHEDULING
      SET 
          QMBD = CASE @aol_type
                     WHEN 'TMA' THEN ISNULL(@QMBD, @qmd)
                     WHEN 'BDIT' THEN ISNULL(@QMBD, @qmd)
                 END,
          QMBDIT = CASE @aol_type
                       WHEN 'TMA' THEN CASE @qb WHEN 'Y' THEN 'Y' ELSE @QBDIT END
                       WHEN 'BDIT' THEN 'Y'
                   END,
          QMCONT1 = CASE @aol_type 
                        WHEN 'TMA' THEN ISNULL(@QCONT1, @qc1)
                        WHEN 'BDIT' THEN ISNULL(@QCONT1, @qc1)
                    END,
          QMCONT2 = CASE @aol_type 
                        WHEN 'TMA' THEN ISNULL(@QCONT2, @qc2)
                        WHEN 'BDIT' THEN ISNULL(@QCONT2, @qc2)
                    END,
          QMCONT3 = CASE @aol_type
                        WHEN 'TMA' THEN ISNULL(@QCONT3, @qc3)
                        WHEN 'BDIT' THEN ISNULL(@QCONT3, @qc3)
                    END,
          QMCONT4 = CASE @aol_type
                        WHEN 'TMA' THEN ISNULL(@QCONT4, @qc4)
                        WHEN 'BDIT' THEN ISNULL(@QCONT4, @qc4)
                    END,
          QMCORPLLCLP = CASE @aol_type 
                            WHEN 'TMA' THEN CASE @qcp WHEN 'Y' THEN 'Y' ELSE @QCORPLLCLP END
                            WHEN 'BDIT' THEN CASE @qcp WHEN 'Y' THEN 'Y' ELSE @QCORPLLCLP END
                        END,
          QMEA = CASE @aol_type 
                      WHEN 'TMA' THEN ISNULL(@QMEA, @qma)
                      WHEN 'BDIT' THEN ISNULL(@QMEA, @qma)
                 END,
          QMEATMA = CASE @aol_type
                        WHEN 'TMA' THEN CASE @qe WHEN 'Y' THEN 'Y' ELSE @QEATMA END
                        WHEN 'BDIT' THEN CASE @qe WHEN 'Y' THEN 'Y' ELSE @QEATMA END
                    END,
          QMEO = CASE @aol_type
                     WHEN 'TMA' THEN ISNULL(@QMEO, @qmo)
                     WHEN 'BDIT' THEN ISNULL(@QMEO, @qmo)
                 END,
          QMEP = CASE @aol_type
                     WHEN 'TMA' THEN ISNULL(@QMEPT, @qmp)
                     WHEN 'BDIT' THEN ISNULL(@QMEPT, @qmp)
                 END,
          QMFROMCONTACT = CASE @aol_type
                              WHEN 'TMA' THEN ISNULL(@CT, @qschct)
                              WHEN 'BDIT' THEN ISNULL(@CT, @qschct)
                          END,
          QMIDITORIDITADM = CASE @aol_type
                                WHEN 'TMA' THEN CASE @qim WHEN 'Y' THEN 'Y'  ELSE @QIDIT END
                                WHEN 'BDIT' THEN CASE @qim WHEN 'Y' THEN 'Y' ELSE @QIDIT END
                            END,
          QMILITADMIN = CASE @aol_type
                            WHEN 'TMA' THEN CASE @qmn WHEN 'Y' THEN 'Y' ELSE @QMILITADMIN END
                            WHEN 'BDIT' THEN CASE @qmn WHEN 'Y' THEN 'Y' ELSE @QMILITADMIN END
                        END,
          QMNVBDT = CASE @aol_type
                        WHEN 'TMA' THEN CASE @qnt WHEN 'Y' THEN 'Y' ELSE @QNVBDT END
                        WHEN 'BDIT' THEN CASE @qnt WHEN 'Y' THEN 'Y' ELSE @QNVBDT END
                    END,
          QMNVTRST = CASE @aol_type
                        WHEN 'TMA' THEN CASE @qnr WHEN 'Y' THEN 'Y' ELSE @QNVTR END
                        WHEN 'BDIT' THEN CASE @qnr WHEN 'Y' THEN 'Y' ELSE @QNVTR END
                     END,
          QMSCHSPECIALINS = CASE @aol_type
                                WHEN 'TMA' THEN ISNULL(@QSCHSPECIALINST, @qsch)
                                WHEN 'BDIT' THEN ISNULL(@QSCHSPECIALINST, @qsch)
                            END,
          QMTMA = CASE @aol_type
                      WHEN 'TMA' THEN CASE WHEN @AOL = 'TMA-Expired' THEN 'N'
                                           WHEN @AOL <> 'TMA-Expired' THEN 'Y' END
                      WHEN 'BDIT' THEN CASE @qa WHEN 'Y' THEN 'Y' ELSE @QTMA END
                  END,
          QMUPDATENOTES = CASE @aol_type
                              WHEN 'TMA' THEN ISNULL(@QMUPDATENOTE, @qnts)
                              WHEN 'BDIT' THEN ISNULL(@QMUPDATENOTE, @qnts)
                          END                
      WHERE Matters = @Matters
      
    END --IF
    BEGIN
      INSERT INTO MattersQSCHEDULING (MattersQSCHEDULING, Matters, QMIDITORIDITADM, QMNVBDT, QMNVTRST, QMTMA, QMBDIT, QMCORPLLCLP,
          QMCONT1, QMCONT2, QMCONT3, QMCONT4, QMBD, QMEA, QMEO, QMEP, QMEATMA, QMILITADMIN, QMSCHSPECIALINS, QMUPDATENOTES, QMFROMCONTACT)
      SELECT 
          CASE @aol_type ....
    END --ELSE
END --IF

Open in new window

2
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.