SQLTeam.com | Weblogs | Forums

Store procedure for leap year

I want to write a store procedure which will check whether entered year is a leap year or not.Kindly help me with this.

hi

put this in a stored procedure .. pass parameter your YEAR ... return Yes/ NO or 1/0

What is the purpose of creating a stored procedure for this? The calculation is trivial and should just be included in the code where it is needed.

For example:

Declare @year int = 2021
      , @isLeapYear bit
      , @isLeapYear2 bit;

    Set @isLeapYear = iif(@year % 400 = 0 Or (@year % 4 = 0 And @year % 100 <> 0), 1, 0);
    Set @isLeaprYear2 = iif(@year & 3 = 0 And (@year % 25 <> 0 Or @year & 15 = 0), 1, 0)

 Select @year, @isLeapYear, @isLeapYear2;

If you want to use the calculation in a query, then you can use CROSS APPLY to calculate the value.

Here is some test code if you want to validate the calculations:

   With t(n)
     As (
 Select t.n 
   From (
 Values (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)
      , (0), (0), (0), (0), (0), (0), (0), (0), (0), (0), (0)) As t(n)
        )
      , years (YearNumber)
     As (
 Select Top (9999)
        checksum(row_number() over(Order By @@spid))
   From t t1, t t2, t t3
        )
      , checkCalcs
     As (        
 Select y.YearNumber
      , isLeapYear = iif(y.YearNumber % 400 = 0 Or (y.YearNumber % 4 = 0 And y.YearNumber % 100 <> 0), 1, 0)
      , isLeapYear2 = iif(y.YearNumber & 3 = 0 And (y.YearNumber % 25 <> 0 Or y.YearNumber & 15 = 0), 1, 0)
   From years                   y
        )
 Select *
   From checkCalcs
  Where isLeapYear <> isLeapYear2;

It's a requirement...I need to create procedure for that query.
For example :

I have a table like this,

Year col1
2015 30
2016 2
2017 6

So For this table I need to create store procedure for leap year and not leap year

If I execute the store procedure,
Exp: EXEC UPS_YEAR '2015'

It should display like this
2015 is not leap year

What others are telling you is that a stored procedure is an overkill. Killing a fly with a sledge hammer

What is the business requirement? Just stating it is a requirement doesn't tell us why it needs to be a stored procedure.

Either way - you have code samples to get the result.

My guess that the business requirement is that it is a school class assignment, given how they worded things.