Hecatonchires
asked on
TOP and Stored Proc parameter
I can select the top 10 rows no problem.
==========================
CREATE PROCEDURE [crmGetTopXActionsForMembe
@memberno int,
@brand int
AS
SELECT TOP 10
[snip]
ORDER BY
dateAdded DESC
GO
==========================
I would like to do this:
==========================
CREATE PROCEDURE [crmGetTopXActionsForMembe
@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?
==========================
CREATE PROCEDURE [crmGetTopXActionsForMembe
@memberno int,
@brand int
AS
SELECT TOP 10
[snip]
ORDER BY
dateAdded DESC
GO
==========================
I would like to do this:
==========================
CREATE PROCEDURE [crmGetTopXActionsForMembe
@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?
'SELECT TOP'+ @X +'
[snip]
ORDER BY
dateAdded DESC'
[snip]
ORDER BY
dateAdded DESC'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Declare @sqlSel as varchar(6000)
set @sqlSel =
'SELECT TOP'+ @X +'
[snip]
ORDER BY
dateAdded DESC'
exec (@sqlSel)
set @sqlSel =
'SELECT TOP'+ @X +'
[snip]
ORDER BY
dateAdded DESC'
exec (@sqlSel)
ASKER
I was a little worried I'd have to set the rowcount back to 0 after that, but works a treat. Thanks acperkins
CREATE PROCEDURE [crmGetTopXActionsForMembe
@memberno int,
@brand int,
@X int,
AS
SET ROWCOUNT TO @X
[snip]
ORDER BY
dateAdded DESC
GO