TOP and Stored Proc parameter

I can select the top 10 rows no problem.
======================================
CREATE PROCEDURE [crmGetTopXActionsForMember]
      @memberno int,
      @brand int
AS
SELECT TOP 10

[snip]

ORDER BY
      dateAdded DESC
GO
======================================
I would like to do this:
======================================
CREATE PROCEDURE [crmGetTopXActionsForMember]
      @memberno int,
      @brand int,
      @X int,
AS
SELECT TOP @X

[snip]

ORDER BY
      dateAdded DESC
GO
======================================
as @X is currently specified in the application as configurable setting.  Unfortunately it throws out at SELECT TOP @X.

Is it possible to use a parameter to specify X in a TOP?

Should I be doing this another way entirely?
LVL 7
HecatonchiresAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Typo, it should be:

CREATE PROCEDURE [crmGetTopXActionsForMember]
     @memberno int,
     @brand int,
     @X int,
AS

SET ROWCOUNT @X
[snip]

ORDER BY
     dateAdded DESC
GO
0
 
Anthony PerkinsCommented:
Do it this way:

CREATE PROCEDURE [crmGetTopXActionsForMember]
     @memberno int,
     @brand int,
     @X int,
AS

SET ROWCOUNT TO @X
[snip]

ORDER BY
     dateAdded DESC
GO
0
 
poayseeCommented:
'SELECT TOP'+ @X +'

[snip]

ORDER BY
     dateAdded DESC'
0
 
poayseeCommented:
Declare @sqlSel as varchar(6000)
set @sqlSel =
'SELECT TOP'+ @X +'

[snip]

ORDER BY
     dateAdded DESC'

exec (@sqlSel)
0
 
HecatonchiresAuthor Commented:
I was a little worried I'd have to set the rowcount back to 0 after that, but works a treat.  Thanks acperkins
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.