This has me brain locked.
1. I've got a stored procedure.
2. It does a select with a couple joins.
I want to take the results of that stored proc and read them into some a table -- temp table would be ideal.
here's my (really tough) constraints... these really complicate things:
1. the stored proc uses dynamic SQL. As a consequence, I don't know the columns it's going to return (it's kind of the whole point of using it).
2. Since the stored proc uses dynamic SQL, it needs to use sp_executesql internally.
What won't work(i don't think?):
1. I can't create a function that returns a table and does a "return select * from....", since you can't (I don't think?) call a stored a stored proc in the return statement of a function
2. I can't just do an "Insert into #xyz exec .." because I can't pre-define the temp table (I think this is needed?).
3. I don't have a linked server. and i'd rather not. All of this runs local in the same instance of SqlServer, and will have to handle loads of volume (each hit will return a few million rows). So (I think?) OPENQUERY is out.
4. OPENROWSET is a possibility, I suppose, but again, everything is running locally. I think that goes through OLEDB (?) and I'm not sure that's even an option.
THOUGHTS??? HELP?? is it even possible??
View Complete Post