0

I am calling a stored procedure in my current one and it returns 3 SELECT statements.

I need the first SELECT only.

How can I achieve this? I'm new to T-SQL, in C# I would just get those variables but SQL doesn't work that way.

Is it possible to get the values it returns?

Mack
  • 2,556
  • 1
  • 26
  • 44
Kpt.Khaos
  • 673
  • 3
  • 14
  • 37

1 Answers1

1

I have 3 possibilities:

1) Make a new stored proc without the last 2 sSELECTs.

2) Pass an optional parameter to the stored proc to indicate if the last 2 SELECTs should run or not. (If the parameter is not passed, the SELECTs will run by default.)

3) Not an elegant solution, but this might work for your purposes: In the second stored proc, output the first SELECT into a global temp table, then SELECT * from that temp table. (This will keep everything the same for anything else using the stored proc.)

In the first stored proc, select * from the temp table after you call the 2nd stored proc. (This will give you the results that you are looking for.) At the end of your first stored proc, drop the global temp table.

Some caveats: If you have several people hitting the stored proc at once, this won't work with a single global temp table- you'll have to figure out some sort of naming convention so that each user gets their own separate table to play with.

If you have a lot of data in the result set, your tempdb database isn't going to be happy.

Dave Jemison
  • 694
  • 3
  • 7
  • I like option 2 best, then if you make changes to the first select, it will happen for both versions. – HLGEM Apr 23 '14 at 18:55