Case.... When - SQL Server Forums

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)

Case.... When

Author

Topic

nvakeel
Yak Posting Veteran

52 Posts

Posted - 2008-02-05 : 13:36:48

I have a proc which updates a column in a table. I am trying to use
Case When expression. Can you help me to write better code in Case..When as I am very new to Tsql.

Select Case
When (upper(@CODEX) <>'R' )OR
(upper(@CODEX) ='R' AND CONVERT(INT,@RES_ADV_NUM) < CONVERT(INT,@TMP_ADV_NUM)) OR
(upper(@CODEX) ='R' AND CONVERT(INT,@END_ADV_NUM) > CONVERT(INT,@TMP_ADV_NUM))
Then
Begin
if LTRIM(RTRIM(@SORT_CODE)) NOT IN ('07','08','7','8')
begin

--SET @TMP_TOT_ADVS = CONVERT(BIGINT,@TMP_TOT_ADVS) + 1
UPDATE MPAS_TMP.DBO.TMP_PRNJ250M_ADV_INTRAN
SET NEW_ADV_NUM = @TMP_END_ADV_NUM
WHERE JOB_INST_ID = @JOB_INST_ID
AND TMP_PRNJ250M2_ADV_TRAN_ID = @INTRAN_ID

SET @TMP_END_ADV_NUM = @TMP_END_ADV_NUM + 1
end

END
When (upper(@CODEX)='R')

BEGIN --------------------7

SET @TMP_TOT_ADVS = CONVERT(BIGINT,@TMP_TOT_ADVS) + 1

IF LTRIM(RTRIM(@SORT_CODE)) NOT IN ('07','08','7','8')
BEGIN
--- SET @TMP_TOT_ADVS = CONVERT(BIGINT,@TMP_TOT_ADVS) + 1
SET @TMP_END_ADV_NUM = @TMP_END_ADV_NUM + 1
UPDATE MPAS_TMP.DBO.TMP_PRNJ250M_ADV_INTRAN
SET NEW_ADV_NUM = @TMP_END_ADV_NUM
WHERE JOB_INST_ID = @JOB_INST_ID
AND TMP_PRNJ250M2_ADV_TRAN_ID = @INTRAN_ID

END

END --------------------7

End
From MPAS_TMP.DBO.TMP_PRNJ250M_ADV_INTRAN WHERE JOB_INST_ID = @JOB_INST_ID
AND TMP_PRNJ250M2_ADV_TRAN_ID = @INTRAN_ID

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-02-05 : 13:53:49

This is where we are going in this thread: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=96806

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources