Sproc to backdate a Date column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Sproc to backdate a Date column?

Author

Topic

dhoom
Starting Member

9 Posts

Posted - 2008-10-18 : 15:41:42

Hi Experts,

I am not sure whether to use a trigger or a sproc for the solution below:
I have a table:

tblProduct
--------------
PK int
pID int
SDate datetime
EDate datetime

when a new row is getting inserted WITH VALUES for @pID AND @SDate:

PK | pID | SDate | EDate
------------------------------------------------------------------
2 | @pID = '24' | @SDate ='10/19/2008' | 12/31/9999

First::check if a row exists with the same pID AND EDate = 2/31/9999'

SELECT *
FROM tblProduct
WHERE pID = @pID AND EDate = '12/31/9999'

eg results:

PK | pID | SDate | EDate
------------------------------------------------------------------
1 | @pID='24'| 10/12/2008 | 12/31/9999

Second::if a row exists then the existing row's EDate should update to (@SDate - 1 day)

update eg result:

PK | pID | SDate | EDate
-----------------------------------------------------------------
1 | @pID | 10/12/2008 | 10/18/2008 (was 12/31/9999 now new SDate - 1 day)

resulting in 2 rows in tblProduct:

PK | pID | SDate | EDate
-----------------------------------------------------------------
1 | 24 | 10/12/2008 | 10/18/2008
2 | 24 | 10/19/2008 | 12/31/9999

I need help creating this sproc or is a trigger preferred on a insert in tblProduct!

Thanks in advance,
~Dhoom

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2008-10-18 : 21:22:48

I am a fan of using stored procedures. I tend to only use triggers for auditing purposes and enforcing complex constraints, never to manipulate data. How is the data getting inserted into the other tables in your database currently?

dhoom
Starting Member

9 Posts

Posted - 2008-10-18 : 21:39:02

Presently I have a stored procedure to insert the data into tblProduct. Can you help me with the complex sproc that I posted earlier.

Thanks,
~Dhoom.

cr8nk
Yak Posting Veteran

77 Posts

Posted - 2008-10-18 : 23:59:32

Sure, I think all you are going to need is an update and a insert statement

CREATE PROCEDURE usp_tblProductInsert
@pID int
,@SDate datetime

AS

UPDATE tblProduct
SET EDate = @SDate - 1
WHERE pID = @pID
AND EDate = '9999/12/31'

INSERT INTO tblProduct
(pID, SDate, EDate)
VALUES
(@pID, @SDate, '9999/12/31')

malaytech2008
Yak Posting Veteran

95 Posts

Posted - 2008-10-19 : 01:37:16

Above query is ok and if u filter the update and insert query using if condition.

malay

dhoom
Starting Member

9 Posts

Posted - 2008-10-19 : 01:47:06

Wow that really worked! Thanks a bunch! Really appreciate the quick response.

~Dhoom

dhoom
Starting Member

9 Posts

Posted - 2008-10-19 : 01:50:56

Thanks cr8nk! Thanks malaytech2008, yes I can add the if condition to this sproc.

~Dhoom

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources