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?
 
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
 
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.