I've pasted the SP below, but i can not get it to output the records sorted by date in descending order. I must be missing something. Tried ordering it but it still doesn't order correctly.
SP - goes through and outputs form responses by moduleID
------------------------------------------------------------------------------------
USE []
GO
/****** Object: StoredProcedure [dbo].[MBG_DynamicForms_ExportFormResults] Script Date: 09/26/2013 15:45:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[MBG_DynamicForms_ExportFormResults]
@ModuleID int
AS
Declare curBubba Cursor
FOR
SELECT Question, DynamicQuestionID
FROM dynamicforms_question
where QuestionType <> 'HTML'
AND QuestionType <> 'HR'
AND InActive=0
AND ModuleID = @ModuleID
AND Question <> 'Please enter Security Code'
ORDER BY SortOrder
FOR READ ONLY;
Declare @Question VarChar(500);
Declare @DynamicQuestionID uniqueidentifier;
Declare @SQLToUse VarChar(8000);
Declare @SQLToUseJoin VarChar(8000);
Declare @SQLToExecute Varchar(8000);
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 IsNull(' + @Alias + '.response,''N/A'') as [' + @Question + '] '
set @SQLToUseJoin = '
From (
SELECT distinct Y.uniqueresponseID, Y.UserID, B.UserName
from [DynamicForms_QuestionResponse] As Y
inner join DynamicForms_Question as Z on (Y.DynamicQuestionID = Z.DynamicQuestionID)
Left Outer Join Users B on B.UserID = Y.UserID
where Z.ModuleID = ' + Cast(@ModuleID as varchar(20)) + '
) As A
Left Outer join 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 [' + @Question + '] '
set @SQLToUseJoin = @SQLToUseJoin + 'Left Outer join 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 = @SQLToUse + @SQLToUseJoin
--set @SQLToExecute = @SQLToUse + @SQLToUseJoin
print @SQLToExecute
EXEC(@SQLToExecute);
------------------------------------------------------------------------------------------
|