Try this and check whether it works
CREATE PROCEDURE dbo.StoredProcedure2
(
@s SMALLDATETIME,
@e SMALLDATETIME
)
AS begin
SET NOCOUNT ON
;WITH n(n) AS
(
SELECT TOP (DATEDIFF(MONTH, @s, @e)+1) ROW_NUMBER() OVER
(ORDER BY [object_id])-1 FROM sys.all_objects
),
leavedepot(n,fd,ld) AS
(
SELECT n.n, DATEADD(MONTH, n.n, m.m), DATEADD(MONTH, n.n+1, m.m)
FROM n, (SELECT DATEADD(DAY, 1-DAY(@s), @s)) AS m(m)
)
SELECT [Month] = DATENAME(MONTH, fd), [Days] = DATEDIFF(DAY, fd, ld)
- CASE WHEN @s > fd THEN (DATEDIFF(DAY, fd, @s)+1) ELSE 0 END
- CASE WHEN @e < ld THEN (DATEDIFF(DAY, @e, ld)-1) ELSE 0 END
FROM leavedepot;
RETURN
end