The Way to Programming
The Way to Programming
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.
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.
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.
Sign in to your account