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
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.
ASKER
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.
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.
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.
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.
ASKER
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
ENDASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That's what I was thinking. To make things worse, it was all on one line!!
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.
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
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.