-1

i have one sp with multiple resultsets like

Create procedure spNAME
As
Begin
select * from t1
select * from t2
select * from t3
END

now i want to create one stored procedure to find result of 3 statement in sp

Yogesh Sharma
  • 49,870
  • 5
  • 26
  • 52
Youthful Channel
  • 9
  • 4
  • Possible duplicate of [Access to Result sets from within Stored procedures Transact-SQL SQL Server](https://stackoverflow.com/questions/58940/access-to-result-sets-from-within-stored-procedures-transact-sql-sql-server). If you have some freedom over how to arrange your sprocs, see [Erland Sommarskog's write-up on the matter](http://www.sommarskog.se/share_data.html). Multiple result sets in one sproc are pretty much the worst case. – Jeroen Mostert Jun 01 '18 at 12:14
  • No bro, it's not duplicate, if you think that it's believe then can you please let me know that how it can execute only 3rd select resultset – Youthful Channel Jun 01 '18 at 12:16
  • Then I'm not at all clear on what you're asking. Executing `spName` will always execute all three statements, unless you squeeze in an `IF` statement and a parameter that tells it to only execute the third statement. But if you can do that you might as well write a new sproc that only contains `SELECT * FROM t3`. If, on the other hand, you want to leave the sproc as-is but have some way of accessing only the third result set, then the dupe is appropriate: all the answers deal with how to access multiple result sets, whether you want to process or ignore them. ("Skipping" one is impossible.) – Jeroen Mostert Jun 01 '18 at 12:18
  • I know it will execute all 3 statements. but i want to show only 3rd statement to user like i want to create one stored procedure, if i pass resultsetid=3 then it will execute only 3rd statement\ – Youthful Channel Jun 01 '18 at 12:22

1 Answers1

1

Re-write your proc something like this..

Create procedure spNAME
    @RS INT = NULL
As
Begin

    IF (@RS = 1)
    BEGIN
     select * from t1
    END

    IF (@RS = 2)
    BEGIN
     select * from t2
    END

    IF (@RS = 3)
    BEGIN
     select * from t3
    END

END

Now pass different parameter values to proc depending on what result set you want to return 

Exec spNAME     --<-- No Result set
Exec spNAME 1   --<-- Result set 1
Exec spNAME 2   --<-- Result set 2
Exec spNAME 3   --<-- Result set 3
M.Ali
  • 67,945
  • 13
  • 101
  • 127
  • no bro. i know this this things, you know when we will execute this sp then it will return 3 resultsets , but i want to see only one specific resultset by using another sp , i will pass only resultsetId & spname then it will return then specific resultset – Youthful Channel Jun 01 '18 at 12:25
  • @YouthfulChannel: this is impossible, because T-SQL has no statements for skipping or selecting result sets. You cannot pass a stored procedure name dynamically and then get only a particular result set from it (unless you leverage things like CLR functions). As far as T-SQL is concerned, a stored procedure is a black box that executes in its entirety. – Jeroen Mostert Jun 01 '18 at 12:39
  • is it possible to use cursor for execution of sp . and we can store results in diff. diff. temp table ? – Youthful Channel Jun 01 '18 at 13:27