May 31, 2011 at 10:27 am
Hi I have 2 procedures where 1 proc does daily and the other monthly update.My seniors want me to make them as one procedure as its getting difficult to modify 2 procedures when there is an issue.
I can handel that but the problem is daily procedure does few extra steps than the monthly.so can you please help me on this.
create proc [dbo].[SP_employee](@daily as varchar(10) = '') as
declare @em datetime
select @em = dbo.fn_monthend
if (@daily = 'Daily' ) ----I added this step
select @em = getdate()----I added this step
truncate table temp
-- populate temp emp with all the employees currently in the system
insert into temp(emp_ID, SUFFIX, AGE_GENDER)
select employee as emp_id, suffix, case gender when 'F' then 1 when 'M' then 2 else 3 end as AGE_GENDER,
phone
from source_temp;
update temp set age = 0 where age < 0
update temp set age = 99 where age > 99
--------monthly procedure ends here------------------------------
-----daily procedure has few extra below steps ------
exec SP_employee_doc;
insert into [dbo].[employee_History]
exec Load_employee
So I need help how to execute monthly at the end of the month and not do the extra steps which daily procedure has .please any help is appreciated.
Thankyou.
May 31, 2011 at 10:34 am
add an input parameter to denote daily versus monthly and only call certain sections when needed
May 31, 2011 at 12:43 pm
Thank you for the quick reply.I am really new to this and would you mind showing an example.
May 31, 2011 at 12:47 pm
start with something like this (might not work exactly as written)
create proc [dbo].[SP_employee](@daily as varchar(10) = '',
@type VARCHAR(10) = 'Daily'
) as
declare @em datetime
select @em = dbo.fn_monthend
if (@daily = 'Daily' ) ----I added this step
select @em = getdate()----I added this step
truncate table temp
-- populate temp emp with all the employees currently in the system
insert into temp(emp_ID, SUFFIX, AGE_GENDER)
select employee as emp_id, suffix, case gender when 'F' then 1 when 'M' then 2 else 3 end as AGE_GENDER,
phone
from source_temp;
update temp set age = 0 where age < 0
update temp set age = 99 where age > 99
--------monthly procedure ends here------------------------------
-----daily procedure has few extra below steps ------
If @type = 'Daily'
BEGIN
exec SP_employee_doc;
insert into [dbo].[employee_History]
exec Load_employee
So I need help how to execute monthly at the end of the month and not do the extra steps which daily procedure has .please any help is appreciated.
END
May 31, 2011 at 1:14 pm
Thank you and I really appreciate your reply.I will try and let you know.
thanks once again.
May 31, 2011 at 1:17 pm
not a problem, we were all in your shoes once.
June 1, 2011 at 7:47 am
Thank you very much.worked perfectly and Really once again thanks a lot.
June 1, 2011 at 7:51 am
excellent, glad I could help
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply