Can I use varible here in Stored Procedure?

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 ..
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"mizi" <haha@.haha.com> wrote in message
news:Xns9718D21D69A72hahahahacom@.207.46.248.16...
> 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!|||"Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in
news:O8eKNjT8FHA.3760@.TK2MSFTNGP14.phx.gbl:

> 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 ..
>
Is there any difference between use 'select top' and 'set rowcount'? for
example, the efficiency?|||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]
"mizi" <haha@.haha.com> wrote in message
news:Xns9718D21D69A72hahahahacom@.207.46.248.16...
> 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!|||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.
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"mizi" <haha@.haha.com> wrote in message
news:Xns9718D507C333hahahahacom@.207.46.248.16...
> "Tony Rogerson" <tonyrogerson@.sqlserverfaq.com> wrote in
> news:O8eKNjT8FHA.3760@.TK2MSFTNGP14.phx.gbl:
>
> Is there any difference between use 'select top' and 'set rowcount'? for
> example, the efficiency?|||mizi <haha@.haha.com> wrote in
news:Xns9718D21D69A72hahahahacom@.207.46.248.16:

> 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!
select 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
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging