2 procedures, work separately but not together

  • 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?

  • 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.

  • 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