Updating Stored Procedure-Case Help

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

Updating Stored Procedure-Case Help

Author

Topic

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-11-06 : 10:12:58

Below is the existing SP to update a datetime column and a char column.Currently whenever i execute the SP it updates the datetime column to current system date

ALTER PROCEDURE [dbo]. [Update_Customer_Approval]
(
@Custid int
)
AS
UPDATE Customer_Info
SET Status = 'A',
APPROVEDT = getdate(),
CONFIRMDT = getdate()
WHERE (reqid = @Custid)

I want to change the stored procedure such that it updates the APPROVEDT and CONFIRMDT only when the value in the column is NULL or 1/1/1900 otherwise it should not update the value

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-11-06 : 14:16:09

Could be done with a CASE expression


SET
     Status = 'A'
    , ApprovedDT = CASE
           WHEN approvedDT IS NULL THEN GETDATE()
           WHEN approvedDT = '19000101' THEN GETDATE()
           ELSE ApprovedDT
           END
    ....
    ....

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-11-07 : 01:25:29

You can use nullif or case statement to implement this.

nullif(approvedDT,APPROVEDT)

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 04:22:19

quote:
Originally posted by jassi.singh

You can use nullif or case statement to implement this.

nullif(approvedDT,APPROVEDT)

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

nullif alone wont work here
see the requirement it says converting NULL or 1/1/1900 to current date value


ALTER PROCEDURE [dbo]. [Update_Customer_Approval]
(
@Custid int
)
AS
UPDATE Customer_Info
SET Status = 'A',
APPROVEDT = COALESCE(NULLIF(APPROVEDT ,'1900-01-01'),getdate()),
CONFIRMDT = COALESCE(NULLIF(CONFIRMDT ,'1900-01-01'),getdate())
WHERE (reqid = @Custid)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2011-11-07 : 08:04:50

I think that either will use CASE under the covers?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 09:19:58

quote:
Originally posted by Transact Charlie

I think that either will use CASE under the covers?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

yep...thats true

I was just showing him the suggestion using NULLIF as he was suggesting it

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jim_jim
Constraint Violating Yak Guru

306 Posts

Posted - 2011-11-07 : 10:44:27

Thank You All.I have implented the one suggested by Visakh and it worked.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 10:46:46

quote:
Originally posted by jim_jim

Thank You All.I have implented the one suggested by Visakh and it worked.

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources