How to do SELECT TOP @Param in a Stored Procedure?

I’m trying to do the following in a proc, but getting an incorrect syntax error:

SELECT TOP @NumberOfResultsToReturn *

What am I doing wrong here? Thanks.

Stored Procedure OUTPUT param returns always NULL

I do have a problem. I am trying to return from the Stored procedure via OUTPUT parameter. alter procedure dbo.UDP_GET_OUT @P_PARAM XML OUTPUT WITH EXECUTE AS CALLER as BEGIN SET @P_PARAM = ( select

How do you select a stored procedure’s return value?

It seems like this would would be trivial, but my SQL is not up to par and I can’t figure it out. I just want to select a returned scalar from a stored procedure. Basically like this: select dbo.sProc

How do I select from a stored procedure in Sybase?

My DBA has constructed me a stored procedure in a Sybase database, for which I don’t have the definition. If I run it, it returns a resultset with a set of columns and values. I would like to SELECT f

How to SELECT * INTO [temp table] FROM [stored procedure]

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBus

How do I delete a stored procedure in postgresql?

How do I delete a stored procedure in postgresql?

How do I make a stored procedure in MS Access?

How do I make a stored procedure in MS Access?

SQL Server stored procedure select top 1 and update in one statement?

I’m trying to select the top 1 record with recordStatus=0 and at the same time update the recordStatus column to 1 in a stored procedure, My question is Can it be done in one select statement or do I

stored procedure insert into select

How can I do the following in oracle? execute stored_procedure (column1, column2) SELECT col1, col2 FROM test Basically, I want to select from a table and pass those rows to oracle stored procedure.

Is it possible to ignore an output param of a stored procedure?

How can I ignore an output parameter of a stored procedure. I’m calling the sp from another sp. e.g.: DECLARE @param1 integer EXEC mystoredprocedure @in_param_1, @in_param2_, @param1 OUTPUT, what do

MySql stored procedures: How to select from procedure table?

Let’s say we have a stored procedure selecting something from a table: CREATE PROCEDURE database.getExamples() SELECT * FROM examples; How can I use the result from this procedure in a later select?

Answers

This is supported in SQL Server 2005 and later, but not in SQL Server 2000. What version are you using?

Add parenthesis:

SELECT TOP (@NumberOfResultsToReturn) *

SQL Server: Put the argument in parens:

SELECT TOP (@NumberOfResultsToReturn) *

You may have to use the RowNumber() method instead.

Here is an example:

DECLARE @PageNum AS INT;
DECLARE @PageSize AS INT;
SET @PageNum = 2;
SET @PageSize = 10;

WITH OrdersRN AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY OrderDate, OrderID) AS RowNum
          ,OrderID
          ,OrderDate
          ,CustomerID
          ,EmployeeID
      FROM dbo.Orders
)

SELECT * 
  FROM OrdersRN
 WHERE RowNum BETWEEN (@PageNum - 1) * @PageSize + 1 
                  AND @PageNum * @PageSize
 ORDER BY OrderDate
         ,OrderID;

EDIT Or you could use parentheses…which I was unaware of at the time 🙂 Thanks guys.

I’m afraid you can’t do this in SQL 2000, but you can try to construct the query

DECLARE @query VARCHAR(500)
set @query = 'SELECT TOP ' + @NumberOfResultsToReturn + '* FROM table'
EXEC @query

I didn’t know the parenthesis trick for SQL 2005, thanks too guys, !!!

Here’s how I was doing it in the old times:

SET @@ROWCOUNT = @NumberOfResultsToReturn
SELECT ........
SET @@ROWCOUNT = 0

This will work, although SELECT TOP (@NumberOfResultsToReturn) is preferable if you’re using SQL server that supports this syntax: