What Should I Use Stored Procedure Or Simple Query ?

This topic has 2 replies, 3 voices, and was last updated 9 years ago by Fierro.
What should I use Stored Procedure Or simple query ?

SapnaVishwas Member September 11, 2015 at 1:00 pm
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
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.
#7680
ShikhaTan Member September 11, 2015 at 1:00 pm

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.

#7681
Fierro Member September 11, 2015 at 1:02 pm

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.

#7683
Viewing 2 reply threads
You must be logged in to reply to this topic.
What should I use Stored Procedure Or simple query ?

Code with C: Your Ultimate Hub for Programming Tutorials, Projects, and Source Codes” is much more than just a website – it’s a vibrant, buzzing hive of coding knowledge and creativity.

Quick Link

Top Categories
