Updation based on set of values

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

General SQL Server Forums

New to SQL Server Programming

Updation based on set of values

Author

Topic

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-11-05 : 00:53:27

Hi i have a table with a column STATUS for which i hardcoded with a value'Open' but now i have to update the column status based on some 5 values theses 5 values are hardcoded manually in the drop down list in the front end how to write the update statement for it

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 02:31:10

pass the selected value as parameter to sql query and use parameter value with case to insert the reqd value

i.e let parameter be @Status, then in code use

CASE @Status
WHEN 'Hardcode Value1' THEN 'YourValue1'
WHEN 'Hardcode Value2' THEN 'YourValue2'
WHEN 'Hardcode Value3' THEN 'YourValue3'
...
WHEN 'Hardcode Value5' THEN 'YourValue5'
END

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-11-05 : 02:48:37

CREATE PROCEDURE USP_USER_VIEW_TASK_LINK
(
@STATUS VARCHAR(20),
@ASSOC_ID VARCHAR(15)
)
AS
BEGIN
DECLARE
@i_RETURNCODE INT,
@a_c_ERRMSG VARCHAR

SELECT
@i_RETURNCODE = 0
UPDATE UST_VIEW_TASK_TEMP
CASE @STATUS
WHEN @STATUS='Pending' then SET STATUS='Pending'

WHERE
ASSOCIATEID=@ASSOC_ID
AND convert(varchar,ALLO.TASK_ALLOC_DATE,112) =convert(varchar,getdate(),112)
IF (@@error <> 0)
BEGIN
SELECT @i_RETURNCODE = 1
SELECT @a_c_ERRMSG = 'Error while Inserting UST_VIEW_TASK_TEMP'
GOTO errorhandler
END
ELSE
RETURN @i_RETURNCODE

errorhandler:

RETURN @i_RETURNCODE
END

IS THIS WAY CORRECT SOMETHINBG MISTAKE IS COMING NEAR CASE

susan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-05 : 02:51:15


CREATE PROCEDURE USP_USER_VIEW_TASK_LINK
(
@STATUS VARCHAR(20),
@ASSOC_ID VARCHAR(15)
)
AS
BEGIN
DECLARE 
@i_RETURNCODE INT, 
@a_c_ERRMSG VARCHAR 

SELECT 
@i_RETURNCODE = 0 
UPDATE UST_VIEW_TASK_TEMP 
SET STATUS=CASE @STATUS
WHEN 'Pending' then 'Pending'
...
WHERE
ASSOCIATEID=@ASSOC_ID
AND convert(varchar,ALLO.TASK_ALLOC_DATE,112) =convert(varchar,getdate(),112) 
IF (@@error <> 0) 
BEGIN 
SELECT @i_RETURNCODE = 1 
SELECT @a_c_ERRMSG = 'Error while Inserting UST_VIEW_TASK_TEMP' 
GOTO errorhandler 
END 
ELSE 
RETURN @i_RETURNCODE 

errorhandler: 

RETURN @i_RETURNCODE 
END

susan_151615
Yak Posting Veteran

99 Posts

Posted - 2008-11-05 : 04:14:15

Hi thank u so much it worked but the case must end with End

susan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources