how to write this sp? - 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

General SQL Server Forums

New to SQL Server Programming

how to write this sp?

Author

Topic

samsun125
Yak Posting Veteran

63 Posts

Posted - 2009-01-12 : 05:39:27

hi all ,

i have one query,i.e
i want to write stored procedure to update date ????????
i have two tables 1.emptable(emp_id,emp_name)
2.datetable(old_date,New_date)
in emptable if i will insert one record i.e 1,rajesh
at the same time i want to create one record in datetable also(getdate(),getdate())
after some time or after some days if i modified that previous record in emptable i.e(1,suresh)at the same time i want to update New_date column in datetable.

please help me how to write stored procedure .

Thanks & Regards
Rama

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-12 : 05:45:58

try this

CREATE PROCEDURE [dbo].[usp_sample]
(
@empid int,
@empname varchar(32)

)
AS
SET NOCOUNT ON
BEGIN

IF NOT EXISTS ( SELECT 1 FROM emptable WHERE empid = @empid )
BEGIN
INSERT INTO emptable
(empid, empname )
VALUES
(@empid,@empname )
INSERT INTO datetable(old_date,New_date) values(getdate(),getdate())
end
else
begin
update emptable
set empname = @empname
where empid = @empid
update datetable
set New_date= getdate()
end
END
SET NOCOUNT OFF

jbp_j
Starting Member

24 Posts

Posted - 2009-01-12 : 05:46:33

hi,

U have to change the datetable(old_date,New_date) structure to
datetable(emp_id,old_date,New_date)

after that u can proceed with the following.

update datetable
set old_date = New_date,
New_date = getdate()
where emp_id = UrId

OR

same thing done By maintaining the all columns in one table only.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-12 : 08:53:03

quote:
Originally posted by samsun125

hi all ,

i have one query,i.e
i want to write stored procedure to update date ????????
i have two tables 1.emptable(emp_id,emp_name)
2.datetable(old_date,New_date)
in emptable if i will insert one record i.e 1,rajesh
at the same time i want to create one record in datetable also(getdate(),getdate())
after some time or after some days if i modified that previous record in emptable i.e(1,suresh)at the same time i want to update New_date column in datetable.

please help me how to write stored procedure .

Thanks & Regards
Rama

I think you are looking for Trigger not SP.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 09:18:19

yeah...sounds like you need an insert update trigger

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 09:23:22

shouldnt you be having emp_id as pk in datetable. else how will you understand which record corresponds to which record in emptable and represents its date?

I feel what you need is just two columns in your main table emptable and a trigger in it to do this updation.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources