How to write IF statement in StoredProcedure that compares integer values

Question

0.00/5 (No votes)

See more:

Hi,
I have a stored procedure that contains a while loop based on integer value i have to loop the while.i.e if a month contains 31 days the while loop should execute 31 times or month contains 30 days the wile loop executes 30 times the following sample code i have written please modify it.

Declare @Days Table (Days int);
Declare @i as int;
Declare @CurrentDate datetime
declare @FirstDay datetime
declare @LastDay datetime
declare @LastDay31 int

Begin

Set @CurrentDate= '2012-08-27'
Set @CurrentDate=(select REPLACE(@CurrentDate,'-','/'));
set @FirstDay=(SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(@CurrentDate)-1),@CurrentDate),111))
Set @FirstDay=(select REPLACE(@FirstDay,'-','/'));
set @LastDay=(SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,@CurrentDate))),DATEADD(mm,1,@CurrentDate)),111))
Set @LastDay=(select REPLACE(@LastDay,'-','/'));
set @LastDay31=(select DAY(@LastDay))

if(LastDay31=31)

Set @i = 1
While @i < 31
Begin
Insert into @Days
Values(@i);
Set @i = @i + 1;
else if

SQL

if(LastDay31=30)

   Set @i = 1
   While @i < 30
   Begin
   Insert into @Days
   Values(@i);
   Set @i = @i + 1;

end if
End;

Posted 27-Aug-12 20:17pm

Madhugundi

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Santhosh Kumar Jayaraman · Answer 1 · 2012-08-27T20:28:00

Try this

SQL

Declare @Days Table (Days int);
Declare @i as int;
Declare @CurrentDate datetime
declare @FirstDay datetime
declare @LastDay datetime
declare @LastDay31 int


 
Set @CurrentDate= '2012-06-27'
Set @CurrentDate=(select REPLACE(@CurrentDate,'-','/'));
set @FirstDay=(SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(@CurrentDate)-1),@CurrentDate),111))
Set @FirstDay=(select REPLACE(@FirstDay,'-','/'));
set @LastDay=(SELECT CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,@CurrentDate))),DATEADD(mm,1,@CurrentDate)),111))
Set @LastDay=(select REPLACE(@LastDay,'-','/'));
set @LastDay31=(select DAY(@LastDay))

if(@LastDay31=31)
Begin
	Set @i = 1
	While @i <= 31
	Begin
			Insert into @Days Values(@i);
			Set @i = @i + 1;
	END
END		
else if(@LastDay31=30)
begin
   Set @i = 1
   While @i <= 30
   Begin
		Insert into @Days Values(@i);
		Set @i = @i + 1;
	ENd 
end

select * from @days

__TR__ · Answer 2 · 2012-08-27T21:06:00

Here are 2 approachs to do this.

SQL

Declare @Days Table (Days int); 

DECLARE @Date DATETIME
SET @Date = '2012-08-27'

DECLARE @Count INT
SET @Count = 1

DECLARE @DaysCount INT
SELECT @DaysCount = datediff(day, dateadd(day, 1-day(@date), @date),
              dateadd(month, 1, dateadd(day, 1-day(@date), @date)))


WHILE @Count <= @DaysCount
BEGIN

	INSERT INTO @Days
	SELECT @Count

	SET @Count = @Count + 1

END

SELECT * FROM @Days

The second approach would be to create a function that will return the number of days for a given date.
found the function here[^]

SQL

CREATE FUNCTION [dbo].[udf_GetNumDaysInMonth] ( @myDateTime DATETIME )
RETURNS INT
AS
BEGIN
DECLARE @rtDate INT
SET @rtDate = CASE WHEN MONTH(@myDateTime)
IN (1, 3, 5, 7, 8, 10, 12) THEN 31
WHEN MONTH(@myDateTime) IN (4, 6, 9, 11) THEN 30
ELSE CASE WHEN (YEAR(@myDateTime) % 4 = 0
AND
YEAR(@myDateTime) % 100 != 0)
OR
(YEAR(@myDateTime) % 400 = 0)
THEN 29
ELSE 28 END
END
RETURN @rtDate
END

and then call the function to get the number of days in a month like this

SQL

Declare @Days Table (Days int);

DECLARE @i INT
SET @i = 1

WHILE  @i <= (SELECT dbo.udf_GetNumDaysInMonth('2012-08-27') NumDaysInMonth)
BEGIN

    INSERT INTO @Days
    SELECT @i

SET @i = @i + 1

END

SELECT * FROM @Days

How to write IF statement in StoredProcedure that compares integer values

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0