I want to write a store procedure which will check whether entered year is a leap year or not.Kindly help me with this.
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.
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,
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.