July 6, 2005 at 7:39 am
So I have 2 procedures:
CREATE PROCEDURE dbo.REPORTING_Event_Report
(
@EventID varchar(500),
@FieldID varchar(500),
@SortBy varchar(500) = null
)
AS
declare @SQLstring nvarchar(4000)
set @SQLstring = 'SELECT FieldID,
FormID,
Name
INTO ##Temp1
FROM dbo.FormFields
where Fieldid in (' + @fieldid + ')'
exec(@SQLstring)
set @SQLstring ='SELECT ISNULL(a.EventID, 0) AS EventID,
r.FieldID,
ISNULL(a.UserID, 0) AS UserID,
ISNULL(a.Response, 0) AS Response
INTO ##Temp1Response
FROM ##Temp1 r LEFT OUTER JOIN dbo.FormFieldResponse a
ON r.FieldID = a.FieldID
and a.EventID in (' + @EventID + ')
and r.FieldID in (' + @FieldID + ')
and a.responseID = (select Max(b.ResponseID)
from dbo.FormFieldResponse b
where b.eventid = a.eventid
and b.fieldid = a.fieldid
and b.userid = a.userid)'
exec(@SQLstring)
set @SqlString = ''
Exec dbo.ReturnResponseByFormSXML @EventID, @FieldID
GO
Which works fine. it creates 2 tables and when fields are empty replaces it with '0'. Then it executes 2nd procedure.
CREATE PROCEDURE dbo.ReturnResponseByFormSXML
(
@EventID varchar(500),
@FieldID varchar(500)
)
AS
DECLARE @SqlString nvarchar(4000)
DECLARE @SqlString2 nvarchar(4000)
set @Sqlstring2 = '
SELECT @Sql = coalesce(@Sql,'''') + '' min(case B.FieldID when '' + ltrim(Str(A.FieldID)) + '' then Response end) as ['' + Name + ''],''
FROM ##Temp1 A
JOIN ##Temp1Response B on A.fieldId = b.FieldID
WHERE a.FieldID in (' + @FieldID + ')
AND B.EventID in (' + @EventID + ')'
exec sp_executeSql @SqlString2, N'@Sql varchar(8000) OUTPUT ', @SqlString OUTPUT
Set @SqlString = SUBSTRING(@SqlString,1, len(@SqlString) -1)
SET @SqlString = 'SELECT EventID, B.UserID, LastName, FirstName, Organization, FormID, ' + @SqlString + '
FROM ##Temp1 A
JOIN ##Temp1Response B on A.fieldId = b.FieldID
JOIN UserInfo C on B.UserID = C.UserID
WHERE A.FieldID in (' + @FieldID + ') AND B.EventID in (' + @EventID + ')
Group by EventID, B.UserID, LastName, FirstName, Organization, FormID
Order by EventID'
exec sp_executeSql @SqlString, N'@FieldID varchar(500), @EventID varchar(500)', @FieldID, @EventID
It builds a table with final results. They use to work. Both of them. And actually it works, but not exactly. When there is no response for specific FieldID that I'm passing in, it won't output it. When there is - it works fine. If I only use 1st procedure - it builds tables no matter what, even if it's empty fields - it replaces NULLs with '0'.
Anyone have any idea why they don't wanna work together?
July 6, 2005 at 8:21 am
The problem was solved by adding set @EventID = @EventID + ',0'
and adding LEFT JOIN on UserInfo.
Now it works, but it gives back multiple identical Responses rows.
July 7, 2005 at 6:35 pm
Just add the keyword DISTINCT after the word SELECT and you'll get rid of the multiple rows.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply