Shivam Sharma Shivam Sharma - 2 months ago 11
SQL Question

Write a stored procedure with conditions

I have two stored procedures

spWeek
and
spMonth
.

I want to write one stored procedure such that when it is week1 or week2 or week3 -the first stored procedure i.e
spWeek
gets executed. Again when it is week4 - both the stored procedures i.e
spWeek
and
spMonth
get executed.

Please help me with the query to write the stored procedure.

Thanks in advance.

Answer

This code will first calculate the week in the current month.

By substracting the week number of the last day in the previous month from the week number of the current date.

That number is used to control which procedures are executed.

CREATE PROCEDURE dbo.spWeeklySometimesMonthly
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @week_in_month INT;
  SET @week_in_month = DATEPART(WEEK,GetDate())-DATEPART(WEEK,DATEADD(DAY,-DATEPART(DAY,GetDate()),GetDate()));

  IF (@week_in_month in (1,2,3,4))
  BEGIN
    exec spWeek;
  END;

  IF (@week_in_month = 4)
  BEGIN
    exec spMonth;
  END;

END

Do note that in Sql Server a week starts on Sunday.
So Saturday 2016-09-03 will have @week_in_month calculated as 0.
While Sunday 2016-09-04 calculates @week_in_month as 1.

Comments