Get top 10 records from sp

Ajay Mavatkar

9y1.1kSQL

Hi,

I have a sp and I need to get top 10 record from sp.

Here is my effort,

DECLARE @Temp TABLE(id INT,Name VARCHAR(20),Phone VARCHAR(20))

INSERT INTO @Temp

EXEC sp_MyListReport

@id, @Name , @Phone

SELECT TOP(10) * FROM @Temp

@id, @Name , @Phone are parameters for sp, but it is not showing any records.

Any help will be appreciated. Thanks in advance

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Saravanan V9y ago
First, directly run the SP separately with required parameter values. This will confirm you that SP returns relevant records or not for the supplied parameters.

eg. EXEC sp_MyListReport 'xx','xxxx','xxxxxx'

Then, before retrieving the top 10 records from temp table, run a select query to check the inserted records.
This way you can easily find out the problem area and change your scripts accordingly.

Let me know if you need any further assistance.

0
Rajkiran Swain9y ago
1. CREATE TABLE ##tmpTable (
2. < datatype >
3. )
4. -- Insert result from the SP to temp table
5. INSERT INTO ##tmpTable
6. EXEC spSelectStudents
9. SELECT TOP 10 * FROM ##tmpTable
0

Manikandan Murugesan9y ago

Refer the following Link : https://blog.sqlauthority.com/2007/04/30/sql-server-set-rowcount-retrieving-or-limiting-the-first-n-records-from-a-sql-query/

https://stackoverflow.com/questions/11471384/getting-top-n-from-stored-procedure