-2

Problem is...I will be provided with a command to execute a procedure...like EXEC SAMPLE_PROCEDURE_NAME, I cannot modify or pass count/number to that procedure

Right now, on execution, the procedure is returning all the rows. I want to limit the number of rows that I receive back

Below are a few things I have tried (Procedure name = Demo4)

This one failed

select top 10 * FROM (EXEC Demo4)

This one failed too

;WITH Results_CTE AS
(
    EXEC Demo4
)
select top 10 *
FROM Results_CTE

This one failed too

DECLARE @tmpNewValue TABLE (*)
INSERT INTO @tmpNewValue 
  EXEC Demo4
select top 10 * FROM @tmpNewValue

I would really appreciate if someone can help on this.

Praveen Prasad
  • 31,561
  • 18
  • 73
  • 106
  • You can't `SELECT` from a Stored Procedure. If you want to limit the number of rows a Procedure returns, you need to put the `TOP` clause in the `SELECT` **inside** the Procedure. Also, make sure you have an `ORDER BY` when using `TOP`; otherwise you'll get any arbitrary rows and the results will not be consistent. – Thom A Aug 29 '20 at 11:26
  • 1
    Perhaps you should add a `@Top` parameter to your Procedure, and then add `TOP (@TOP)` and an `ORDER BY` in your stored procedure's definition. – Thom A Aug 29 '20 at 11:28
  • 2
    I cannot modify procedure – Praveen Prasad Aug 29 '20 at 11:28
  • Then you'll have to define your temporary table/table variable properly (with columns names and data types) and then `SELECT` from that with a `TOP` and `ORDER BY`; but changing the definition of the Procedure is the right solution here in my opinion, – Thom A Aug 29 '20 at 11:31
  • Different users will enter different procedure names, we are not allowed to modify the procedure....expectation is.... user enters a procedure name, we execute and return the top 10 rows of whatever we receive. Don't even know the table structure – Praveen Prasad Aug 29 '20 at 11:34
  • A similar question was answered [here](https://stackoverflow.com/a/654418/724039). It is creating a temp table, problem remains that you, somehow, need to the structure of this temp table. – Luuk Aug 29 '20 at 11:37
  • 1
    This has a strong smell of an [XY Problem](http://xyproblem.info) then. As I first mentioned, you can't `SELECT` from a procedure. Considering you don't even know the definition, then you don't even have a viable `ORDER BY` clause. You either need to **properly** define the table you `INSERT` into, or `ALTER` the procedure(s). Considering you don't know the definition, you're left with the latter. – Thom A Aug 29 '20 at 11:38
  • 1
    What application are you using to execute the proc? You could process only the first 10 rows returned in the client application. There is no T-SQL solution that can limit the number of rows of any arbitrary proc with an unknown result set schema, unless you use SQLCLR. – Dan Guzman Aug 29 '20 at 12:11
  • Any solution that you may get that doesn't involve modifying the procedure itself may end up having terrible performance. This is like bringing 10 tons of water across the country, to only drink a glass of it and throw the rest away. Huge waste of resources. – The Impaler Aug 29 '20 at 13:06

2 Answers2

2

You need to specify the columns in the result set to put them in a table. So:

declare @tmpNewValue table (
    col1 type1,
    col2 type2,
    . . .
);

Then you can insert the rows:

INSERT INTO @tmpNewValue 
      EXEC Demo4;

And return 10 arbitrary rows:

select top 10 *
from @tmpNewValue;

You need an ORDER BY to get ten specific rows (like "first" whatever that means).

If you want them in insertion order and have no other method, then you can use an identity column in your table:

declare @tmpNewValue table (
    id int identity(1, 1),
    col1 type1,
    col2 type2,
    . . .
);

insert into @tmpNewValue (col1, col2, . . . )   -- no `id` column here
      exec Demo4;

Then:

select top (10) *
from @tmpNewValue
order by id;

I should add that I strongly discourage returning result sets like this. Stored procedures should not be viewed as queryable objects. Code can break just because someone makes a small modification to the stored procedure -- like adding debugging or auditing code.

There are other ways to handle these situations:

  • If multiple rows are not needed, then OUTPUT parameters can be used.
  • In many cases, the stored procedure can be written as a user-defined function.
  • You can pass in a table variable to return a table (although that requires a user-defined type).
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • 4
    It is unfortunate that the OP invalidates everything in this answer in the comments, rather than adding all their (odd) requirements in the actual content of the question, as I completely agree with everything here. For example, the OP completely omits that they can't change the definition and that the executed SP is "dynamic" in the question content. Both of these facts completely change the requirements (and have a strong code smell of that weren't not being told the real story). – Thom A Aug 29 '20 at 12:06
  • This is a workaround that can work for the short term, tough it could be a bad solution for the long term. – The Impaler Aug 29 '20 at 13:10
  • This will not work. It assumes that I already know what data this Procedure will return. Requirement is...different users will enter different procedure names, we are not allowed to modify the procedure....expectation is.... user enters a procedure name, we execute and return the top 10 rows of whatever we receive. Don't even know the table structure – – Praveen Prasad Aug 29 '20 at 13:29
  • 1
    @PraveenPrasad . . . If you want to use the data afterwards, you *need* to know the table structure. It is that simple. It sounds like you need to revisit your entire architecture -- especially if the same stored procedure can return different result sets at different times. – Gordon Linoff Aug 29 '20 at 13:30
  • 2
    *"It assumes that I already know what data this Procedure will return."* because you don't tell people in the question that information, @PraveenPrasad . You explain all the caveats in the comments, which is the wrong place. But I stand by that this is clearly an [XY Problem](http://xyproblem.info). – Thom A Aug 29 '20 at 14:02
  • @GordonLinoff We have sort of SQL-Editor, where user can enter their queries...now, before executing the queries we wrap our queries so that they return only maximum100 rows. When the user enters a simple select query we have a working solution, but when the user enters a procedure we are not able to limit the result – Praveen Prasad Aug 29 '20 at 14:46
  • 1
    Again, because it's up to the SP to implement the `TOP`, @PraveenPrasad . You're treating `EXEC` like it's a `SELECT` when it it's nothing like one – Thom A Aug 29 '20 at 15:29
  • @Larnu, got your point. Is it possible that it can return just any 10 rows? May not be the top 10 – Praveen Prasad Aug 29 '20 at 16:19
  • If you're asking that, you're missing my point, @PraveenPrasad . – Thom A Aug 29 '20 at 17:10
0

You can limit the rows from the stored procedures just by setting the row count before running the the stored procedure and releasing it later as below

Solution: SET ROWCOUNT 50; EXEC Demo4; SET ROWCOUNT 0;

Description: Running SET ROWCOUNT 50; would limit the result of any query including stored procedures. then you execute the stored procedure as EXEC Demo4; to release the connection from limiting the rows to 50 you have to set it to Zero as SET ROWCOUNT 0;

Deepak N
  • 1,408
  • 3
  • 15
  • 37