I am trying to add the form parameter user id to the sql query in demonstration 13.
Can someone modify this sproc to include the USERID on the Form. I would like to pass the moduleid and the userid
What is the proper way to pass that variable from a Dynamic Form
Thanks,
Here is the sproc in the example.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE Procedure [dbo].[DynamicForms_ExportFormResults_ForTraining]
@ModuleID int
AS
Declare curBubba Cursor
FOR
SELECT Question, DynamicQuestionID
FROM dynamicforms_question
where QuestionType <> 'HTML'
AND QuestionType <> 'HR'
AND InActive=0
AND ModuleID = 1113
AND Question <> 'Postal 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 = Convert(varchar(8000), @SQLToUse) + Convert(varchar(8000), @SQLToUseJoin)
--set @SQLToExecute = @SQLToUse + @SQLToUseJoin
--print @SQLToExecute
EXEC(@SQLToExecute);