Alter Procedure {databaseOwner}[{objectQualifier}DataSprings_DynamicViews_GetDynamicFormsResults]

@ModuleID int,
@UserID int,
@MySQL nvarchar(MAX) OUTPUT

AS
Declare curBubba Cursor
FOR
SELECT  ShortFieldName, DynamicQuestionID
FROM {databaseOwner}[{objectQualifier}dynamicforms_question]
where QuestionType <> 'HTML' 
AND QuestionType <> 'HR' 
AND Question NOT LIKE '%('
AND Question NOT LIKE ')%'
AND InActive=0 
AND ModuleID = @ModuleID
AND ShortFieldName NOT IN (Select ShortFieldName from {objectQualifier}DynamicForms_Question where ModuleID = @ModuleID
And InActive=0
Group By ShortFieldName
Having Count(*) > 1)

ORDER BY SortOrder
FOR READ ONLY;

Declare @Question VarChar(500);
Declare @DynamicQuestionID uniqueidentifier;
Declare @SQLToUse VarChar(MAX);
Declare @SQLToUseJoin VarChar(MAX);
Declare @SQLToExecute Varchar(MAX);
Declare @Alias   VarChar(10);

Declare @intCounter Int;

set @Question = '';
set @SQLToUse = '';
set @intCounter = 0;
set @Alias = '';
set @SQLToUseJoin = '';

OPEN curBubba;
FETCH NEXT FROM curBubba INTO @Question, @DynamicQuestionID;

WHILE @@FETCH_STATUS = 0
BEGIN

                set @intCounter = @intCounter + 1;

                set @Alias = '[RJoin_' + CAST(@intCounter as varchar(20)) + ']';

                if DataLength(@SQLToUse) = 0
                BEGIN
                                set @SQLToUse = 'SELECT A.UniqueResponseID as UniqueID, A.UserID, IsNull(' + @Alias + '.response,''N/A'') as [' + lower(@Question) + '] '
                                set @SQLToUseJoin = ' 
From (
  SELECT  distinct Y.uniqueresponseID, Y.UserID, B.UserName
  from {databaseOwner}[{objectQualifier}DynamicForms_QuestionResponse] As Y
  inner join {databaseOwner}[{objectQualifier}DynamicForms_Question]  as Z on (Y.DynamicQuestionID = Z.DynamicQuestionID)
  Left Outer Join {databaseOwner}[{objectQualifier}Users] B on B.UserID = Y.UserID 
  where ((Y.UserID = ' + Cast(@UserID as varchar(10)) + ') OR (' + Cast(@UserID as varchar(10)) + ' = -2)) AND Z.ModuleID = ' + Cast(@ModuleID as varchar(20)) + ' 
) As A 
Left Outer join {databaseOwner}[{objectQualifier}DynamicForms_QuestionResponse] as ' + @Alias + ' on (A.uniqueresponseID = ' + @Alias + '.uniqueresponseID and ''' + Cast(@DynamicQuestionID as varchar(50))+ ''' = ' + @Alias + '.DynamicQuestionID)
'
                END
                ELSE
                BEGIN
                                set @SQLToUse = @SQLToUse + ', ' + @Alias + '.response as [' + lower(@Question) + '] '
                                set @SQLToUseJoin = @SQLToUseJoin + 'Left Outer join {databaseOwner}[{objectQualifier}DynamicForms_QuestionResponse] as ' + @Alias + ' on (A.uniqueresponseID = ' + @Alias + '.uniqueresponseID and ''' + Cast(@DynamicQuestionID as varchar(50))+ ''' = ' + @Alias + '.DynamicQuestionID) '
                END;      

                FETCH NEXT FROM curBubba INTO @Question, @DynamicQuestionID;
END

CLOSE curBubba;
DEALLOCATE curBubba;

Set @SQLToExecute = Convert(varchar(MAX), @SQLToUse) + Convert(varchar(MAX), @SQLToUseJoin) 


SET @MySQL = @SQLToExecute
RETURN