write a stored procedure which return all record for last month

  • Thread starter Thread starter Lloyd Dupont
  • Start date Start date
L

Lloyd Dupont

I have a blog_text table with a date column.
I'm trying to write a stored which find the month ot the last entry, and the
return all records for the month.
I'm quite new to stored procedure, can't find relevant infomation in the
documentation, and I'm not managing it....

If anyonw could gives me any hinth on why the below code is wrong and how to
make it both
- compile
- return last month entries...
thanks ;-)
---------
CREATE PROCEDURE dbo.LastBlogMonth AS

DECLARE @max_date DATETIME

SELECT @max_date = MAX([date]) FROM galador_blog_text
GO

SELECT * FROM galador_blog_text
WHERE [date] >= @max_date
RETURN

GO
 
If you want everything for a month, you are most of the way there. Getting
the MAX(Date) is a good start. Next, you will want to use the MONTH() and
YEAR() functions to get all records where the Month of the date = max(date)'s
month and the year matches max(date)'s year. This will only give you records
for a single month.


---

Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

***************************
Think Outside the Box!
***************************
 
I found it, it was the GO between my 2 SELECTs which invalidate the DECLARE
statement!
and yup, MONTH & DATE were welcome, hey ;-)

here is the solution (for the record):
--
CREATE PROCEDURE dbo.gb_LastBlogMonth
@blogname CHAR(20) = 'test'
AS
DECLARE @max_date DATETIME

SELECT @max_date = MAX([date]) FROM galador_blog_text

SELECT *, dbo.gb_num_Comment(ID) as num_comment FROM galador_blog_text
WHERE blog_name LIKE @blogname
AND MONTH([date]) = MONTH (@max_date)
AND YEAR([date]) = YEAR(@max_date)
GO
 
Back
Top