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);
------------------------------------------------------------------------------------------
|