Hi.

There are two webpages in my website. one displays 10 articles last updated
and another one displays 6 articles last updated. So I want to use only one
procedure to do this, and wrote the following code:

CREATE PROCEDURE [dbo].[articleLastUpdated]
(
@nNumber smallint
)
AS
SELECT TOP @nNumber Title, Link From MYARTICLES Order By DATEADDED Desc;
GO

Unfortunately, SQL always prompt me a Syntax error. Is there any way to do
it? Or I must to write two procedures? thanks!

Hi,

If you are using SQL 2005 you can do this...

declare @rows int

set @rows = 10

select top (@rows) * from sys.objects


Remember you need the brackets round @rows!

If not, you can use set rowcount...

SET ROWCOUNT @nNumber

SELECT Title, Link ... ORDER ..

Show quoteHide quote

Show quote Hide quote Is there any difference between use 'select top' and 'set rowcount'? for
example, the efficiency?

Yes there is, also depends what you are doing and whats indexed before you
notice.

Doing a test on a table with 730,000 rows in it i ran the following SQL and
the plans where very similar except the sort step on the TOP had 10 rows,
the ROWCOUNT had the whole table, the IO statistics are as follows:-

With TOP...
Table 'mb_message'. Scan count 1, logical reads 11889, physical reads 7,
read-ahead reads 11694.
(10 row(s) affected)

Table 'mb_message'. Scan count 1, logical reads 11889, physical reads 4,
read-ahead reads 1978.
(10 row(s) affected)

Basically they are the same, the read-ahead are different because a lot of
the data would have been in the cache from the previous read.

Personally, if you are using 2000 i'd use SET ROWCOUNT simply because you
don't have to use dynamic SQL which would mean you have to permission the
base tables to the user instead of just execute on the stored procedure,
other than that - use 2005 :)

Tony.

Show quoteHide quote

you could do this

CREATE PROCEDURE [dbo].[articleLastUpdated]
(
@nNumber smallint
)
AS
exec('SELECT TOP ' + @nNumber + ' Title, Link From MYARTICLES Order By
DATEADDED Desc;')
GO


regards

Michel Posseth [MCP]



Show quoteHide quote

mizi <h***@haha.com> wrote in
Show quoteHide quoteselect top (@variable) was introduced in sql server 2005. Note the
parentheses. For SQL server 2000 and before you have to use:

set rowcount @variable
select title, link...
set rowcount 0

Dynamic Stored Procedure
Dynamic WHERE & Dates
updating column
help with joining on an index
Run 2nd SQL in stored proc