CODEWITHSUNDEEP
sqlsql-serverstored-proceduresparameters
bopapa_1979
bopapa_1979

Reputation: 9237

Desired number of result rows as parameter in stored procedure

I'd like to allow my consumer to specify the number of rows they want returned in a stored procedure. I'm looking to mimic this behavior:

SELECT  TOP 100 AccountId ,
        AccountName
FROM    dbo.Account

But in this fashion:

DECLARE @resultCount INT = 100;

SELECT  TOP @resultCount AccountId ,
        AccountName
FROM    dbo.Account

Of course the 2nd version results in an "Incorrect syntax near @resultCount" error. Is there a way to do this without breaking down to contatenating SQL strings and using EXEC? I find that is not very maintainable.

Upvotes: 2

Views: 2685

Answers (2)

xQbert
xQbert

Reputation: 35333

B. Using TOP with a variable The following example uses a variable to specify the number of employees that are returned in the query result set. Transact-SQL

USE AdventureWorks2012;
GO
DECLARE @p AS int = 10;
SELECT TOP (@p) JobTitle, HireDate, VacationHours
FROM HumanResources.Employee
ORDER BY VacationHours DESC
GO

So in your case...

SELECT  TOP (@resultCount) AccountId ,
        AccountName
FROM    dbo.Account

Upvotes: 1

KM.
KM.

Reputation: 103607

Add parenthesis ( ) around @resultCount :

DECLARE @resultCount INT = 100;

SELECT  TOP (@resultCount) AccountId ,
        AccountName
FROM    dbo.Account

Upvotes: 11

Related Questions