0

I have Stored procedure spSelectStudents is being used like a view and I have no rights to alter it.

I need something like "exec top 10 spSelectStudents"

Ajay2707
  • 5,690
  • 6
  • 40
  • 58
anaval
  • 1,130
  • 10
  • 24
  • 2
    Depending on how much work is being done generating the whole result set versus your top 10, try to make the argument for having it done in procedure rather than getting all of the results only to discard the ones you don't care about. – Ben Thul Jan 04 '16 at 04:46
  • @BenThul: Rather than breaking the signature of the existing SP, propose investigating whether the top-level query in that SP can be made an externally visible *inline table-valued-function* (perhaps called tvfSelectStudents) with the same parameters. Then you could run the SQL like this: `select top 10 from tvfSelectStudents() as tvf`. – Pieter Geerkens Jan 04 '16 at 04:52
  • 1
    @PieterGeerkens: Depending on the internals of the SP, that may or may not be an option. Since we have nothing to go on, I can't recommend that without knowing more. – Ben Thul Jan 04 '16 at 04:55
  • @BenThul: Exactly. I couldn't tell from your comment if you were aware of this possibility, so cc'ed you. – Pieter Geerkens Jan 04 '16 at 04:56

2 Answers2

1

If you know what results you're getting from the stored procedure, you can always create a (temporary) table (with columns corresponding to the results of executing the stored procedure) and

INSERT #tmptable EXEC spSelectStudents

then query the temporary table in whatever way you want to. An alternative is using OPENROWSET, but that has its own issues and permission requirements.

ZLK
  • 2,864
  • 1
  • 10
  • 7
1

Below script will move the result of the procedure spSelectStudents to a temporary table ##tmpTable 

 CREATE TABLE ##tmpTable (
  <Your Colums> < datatype >
 )
-- Insert result from the SP to temp table
INSERT INTO ##tmpTable
EXEC spSelectStudents


SELECT TOP 10 * FROM ##tmpTable

Reference Here

Community
  • 1
  • 1
Shiju Shaji
  • 1,682
  • 17
  • 24