Re: Is there anything like DoEvents in a stored proc?

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: David Portas (REMOVE_BEFORE_REPLYING_dportas_at_acm.org)
Date: 05/10/04 

Date: Mon, 10 May 2004 22:36:59 +0100

You want (at most) the top 104000 rows by date but only complete months? Try
this:

DECLARE @dt DATETIME

SET ROWCOUNT 104001

SELECT @dt = DATEADD(MONTH,DATEDIFF(MONTH,-1,xdate),0)
 FROM SomeTable
 ORDER BY xdate DESC

SET ROWCOUNT 0

SELECT *
 FROM SomeTable
 WHERE xdate >= @dt

I'll be the first to admit that this is far from perfect. It uses a
proprietary, undocumented trick that isn't guaranteed to work in future
versions of SQLServer. I'm sure there are better methods of getting the
result you need but without a full understanding of your business
requirements this is just intended to illustrate one possibility. 

-- 
David Portas
SQL Server MVP
--

Relevant Pages

  • What is wrong with this script
    ... SET ROWCOUNT 0 ... DECLARE @String nvarchar ... SELECT name AS constraintname, ... --Setting the rowcount to one allows for one row from the temp table to be picked off at a time. ...
    (microsoft.public.sqlserver.programming)
  • Re: Speed up UDF
    ... how would I use an inline udf in an existing stored procedure? ... SELECT XDate FROM TimeFormat ... After all you're inputting a DATETIME and returning a ... @groupformat varchar--DAY, WEEK, MONTH ...
    (comp.databases.ms-sqlserver)
  • Re: SELECT TOP with variable not working
    ... SET ROWCOUNT @Num_records ... SQL Server MVP ... "Paul" wrote in message ... > Declare @Num_records as INTEGER ...
    (microsoft.public.sqlserver.server)
  • Re: Select TOP dynamic...
    ... wait for SQL Server 2005, which will allow expressions in the TOP ... use SET ROWCOUNT: ... DECLARE @rc INT ...
    (microsoft.public.sqlserver.programming)
  • RE: Unknown Token error in UDF
    ... use northwind ... returns datetime ... "Dale" wrote: ... > set rowcount 1 ...
    (microsoft.public.sqlserver.programming)