CODEWITHSUNDEEP
sql-serverstored-procedures
jackfrost5234
jackfrost5234

Reputation: 229

SQL Result sets in stored procedures

I currently have 2 stored procedures that I'm trying to combine into 1. The first SP should only ever return 1 record from my DB.

The 2nd SP returns many records(31k+).

Currently I'm just inserting my data into a temp table with a query like:

insert @Results3
   select r2.d_key
   select r1.name
   select r1.comments
   select r1.time
   select r1.more select statements....
from (select distinct * from @results1) as r1, (select * from @results2) as r2

This works, and gets me my data, but the data from the R1 table will only ever return 1 result. However my query will return that same result for each record in my R2 table.

Basically the desired result is this:

d_key, name, comments, time
1, test_name, 'some comment', 10:00:00
2, null, null, null 
3, null, null, null
4, null, null, null

etc.

Instead of:

d_key, name, comments, time
1, test_name, 'some comment', 10:00:00
2, test_name, 'some comment', 10:00:00
3, test_name, 'some comment', 10:00:00
4, test_name, 'some comment', 10:00:00

Is there a better way to do this? I'd rather not get the same data 31k times that I don't need.

Upvotes: 1

Views: 56

Answers (2)

strickt01
strickt01

Reputation: 4048

Without getting into why you would want the results set output in the way you have described, and assuming that you want the values from r1 only output to the first row, this query should do the job:

insert @Results3
select r2.d_key,
r1.*
from (select * from @results2) r2
left outer join (select * from @results1) r1
on r2.d_key = (select min(d_key) from results2)

Upvotes: 0

Josh
Josh

Reputation: 1744

If the stored procedure is going to be consumed from an application that you control then you can use Multiple Active Result Sets (MARS). You'll need to update the code and connection string accordingly. The link below explains the concept and then walks you through implementation.

https://msdn.microsoft.com/en-us/library/ms131686(v=sql.120).aspx

Upvotes: 1

Related Questions