MS SQL Server join on Table and strored procedure results

I have a very good solution from another expert that does a dynamic sql and returns a variable number of columns based on other input.

I placed that solution in a storedprocedure

Is there a way to do a select on a table with a join to the output from the stored procedure

If I knew the columns I could create table #temp
and insert into it with an exe on the procedure

But the variable length nature of the response precludes that
Larry Bristersr. DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
No. Cause one of the premisis is relational theory. A relation is a tuple of a known, fixed number of attributes. Thus there is no built-in support in SQL Server and no way in ANSI SQL to do this.

The only way would be creating a table instead to output the result with a SELECT in your procedure. But this raises permission issues.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Larry Bristersr. DeveloperAuthor Commented:
And its not possible to crate a #temp table using these columns and setting each to a varchar(100) type?

DECLARE @cols VARCHAR(MAX);

SELECT   @cols = ISNULL(@cols + ',', '') + '[' + QuestionType + ']'
FROM     vwPromotionSurveyIndividualAnswersWithID
GROUP BY QuestionType;
0
ste5anSenior DeveloperCommented:
Sure, but due to the scope, you need to create it before you call the procedure.. so it won't work.
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Larry Bristersr. DeveloperAuthor Commented:
Well just for grins
How would I create that table?

I will be awarding points shortly
0
ste5anSenior DeveloperCommented:
By creating a "temporary table", thus creating a normal table in tempdb using the same dynamic approach for the columns as for creating your pivot.
0
Larry Bristersr. DeveloperAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.