can you help me improve the code

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

can you help me improve the code

Author

Topic

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-15 : 08:32:57

Is there any possibility to improve this code, i mean can these two updates be joined as single? or any room for improvement?

UPDATE TVAR
SET
TVAR.SN_230 = 1
,TVAR.MCEA_DUES_CK = (CASE WHEN ISNULL(TPOP.CD_A,'') = '1' THEN ISNULL(TPOP.D_A,0.00) END)
,TVAR.CREDIT_UNION_CK = (CASE WHEN ISNULL(TPOP.CD_A,'') = '2' THEN ISNULL(TPOP.D_A,0.00) END)
,TVAR.AFSCME_DUES_CK = (CASE WHEN ISNULL(TPOP.CD_A,'') = '3' THEN ISNULL(TPOP.D_A,0.00) END)
TVAR.WS_SORT_CODE_SF = CASE
WHEN ISNULL(TPOP.CD_A,'') = '7' THEN 62
WHEN ISNULL(TPOP.CD_A,'') = '8' THEN 61

FROM
TMP_VARIABLES TVAR ,
TMP_POP_PRNA TPOP
WHERE
TPOP.RCD_CD = TVAR.RCD_CD
AND TPOP.JOB_INST_ID = TVAR.JOB_INST_ID
AND CAST(ISNULL(TPOP.CD_A,'0') as int) NOT IN (9, 12)

IF (@@ROWCOUNT <> 0)
BEGIN
EXEC sp_PRNJ_B34181_0230_RETIREE_SORT_RELEASE_081M @job_inst_id
UPDATE TVAR
SET
TVAR.SSN_230 = 0
FROM
TMP_VARIABLES TVAR ,
TMP_POP_PRNA TPOP
WHERE
TPOP.RCD_CD = TVAR.RCD_CD
AND TPOP.JOB_INST_ID = TVAR.JOB_INST_ID
AND CAST(ISNULL(TPOP.CD_A,'0') as int) NOT IN (9, 12)
END

UPDATE TVAR
SET
TVAR.SN_230 = 1
,TVAR.MCEA_DUES_CK = (CASE WHEN ISNULL(TPOP.CD_B,'') = '1' THEN ISNULL(TPOP.D_B,0.00) END)
,TVAR.CREDIT_UNION_CK = (CASE WHEN ISNULL(TPOP.CD_B,'') = '2' THEN ISNULL(TPOP.D_B,0.00) END)
,TVAR.AFSCME_DUES_CK = (CASE WHEN ISNULL(TPOP.CD_B,'') = '3' THEN ISNULL(TPOP.D_B,0.00) END)

FROM
TMP_VARIABLES TVAR ,
TMP_POP_PRNA TPOP
WHERE
TPOP.RCD_CD = TVAR.RCD_CD
AND TPOP.JOB_INST_ID = TVAR.JOB_INST_ID
AND CAST(ISNULL(TPOP.CD_B,'0') as int) NOT IN (9, 12)

IF (@@ROWCOUNT <> 0)
BEGIN
EXEC sp_PRNJ_B34181_0230_RETIREE_SORT_RELEASE_081M @job_inst_id
UPDATE TVAR
SET
TVAR.SSN_230 = 0
FROM
TMP_VARIABLES TVAR ,
TMP_POP_PRNA TPOP
WHERE
TPOP.RCD_CD = TVAR.RCD_CD
AND TPOP.JOB_INST_ID = TVAR.JOB_INST_ID
AND CAST(ISNULL(TPOP.CD_B,'0') as int) NOT IN (9, 12)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-15 : 08:44:53

Try like this:-

UPDATE TVAR
SET 
TVAR.SN_230 = 1 
,TVAR.MCEA_DUES_CK = (CASE WHEN CAST(ISNULL(TPOP.CD_A,'0') as int) NOT IN (9, 12) AND  ISNULL(TPOP.CD_A,'') = '1' THEN ISNULL(TPOP.D_A,0.00) 
WHEN CAST(ISNULL(TPOP.CD_B,'0') as int) NOT IN (9, 12) AND ISNULL(TPOP.CD_B,'') = '1' THEN ISNULL(TPOP.D_B,0.00)
END)
,TVAR.CREDIT_UNION_CK = (CASE WHEN CAST(ISNULL(TPOP.CD_A,'0') as int) NOT IN (9, 12) AND ISNULL(TPOP.CD_A,'') = '2' THEN ISNULL(TPOP.D_A,0.00) 
WHEN CAST(ISNULL(TPOP.CD_B,'0') as int) NOT IN (9, 12) AND ISNULL(TPOP.CD_B,'') = '2' THEN ISNULL(TPOP.D_B,0.00) END)
,TVAR.AFSCME_DUES_CK = (CASE WHEN CAST(ISNULL(TPOP.CD_A,'0') as int) NOT IN (9, 12) AND ISNULL(TPOP.CD_A,'') = '3' THEN ISNULL(TPOP.D_A,0.00) 
WHEN CAST(ISNULL(TPOP.CD_B,'0') as int) NOT IN (9, 12) AND ISNULL(TPOP.CD_B,'') = '3' THEN ISNULL(TPOP.D_B,0.00) END)
TVAR.WS_SORT_CODE_SF = CASE 
WHEN CAST(ISNULL(TPOP.CD_A,'0') as int) NOT IN (9, 12) AND ISNULL(TPOP.CD_A,'') = '7' THEN 62
WHEN CAST(ISNULL(TPOP.CD_A,'0') as int) NOT IN (9, 12) AND ISNULL(TPOP.CD_A,'') = '8' THEN 61

FROM 
TMP_VARIABLES TVAR ,
TMP_POP_PRNA TPOP
WHERE 
TPOP.RCD_CD = TVAR.RCD_CD
AND TPOP.JOB_INST_ID = TVAR.JOB_INST_ID

IF (@@ROWCOUNT <> 0) 
BEGIN
EXEC sp_PRNJ_B34181_0230_RETIREE_SORT_RELEASE_081M @job_inst_id 
UPDATE TVAR
SET 
TVAR.SSN_230 = 0 
FROM 
TMP_VARIABLES TVAR ,
TMP_POP_PRNA TPOP
WHERE 
TPOP.RCD_CD = TVAR.RCD_CD
AND TPOP.JOB_INST_ID = TVAR.JOB_INST_ID
AND (CAST(ISNULL(TPOP.CD_A,'0') as int) NOT IN (9, 12) 
OR CAST(ISNULL(TPOP.CD_B,'0') as int) NOT IN (9, 12))
END