Preview Data requires values for optional SP parameters RRS feed

  • Question

  • I have a stored procedure that has 12 optional parameters (all are initialized as NULL):

    SELECT Agreements.ID, PursuitID, NDA_TA, CompanyID, Org_Division, Org_Group, Org_SBU, Org_SBE,EffDate, EffPeriod, ExpDate, AttAOverview, NegNotes, ExecutedBy, AgreementFile,
           COTS_BP, PrimeSub, Purpose, CompanyName, DivisionName, GroupName, SBUName, SBEName, case PrimeSub when '1' then 'Prime' else 'Sub' end AS Prime,
           case Awarded when '1' then 'Yes' else 'No' end AS Award
    FROM Agreements
    INNER JOIN Pursuits ON Pursuits.ID = PursuitID
    INNER JOIN Org_Divisions on Org_Divisions.ID = Org_Division
    INNER JOIN Org_Groups on Org_Groups.ID = Org_Group
    INNER JOIN Org_SBUs on Org_SBUs.ID = Org_SBU
    INNER JOIN Org_SBEs on Org_SBEs.ID = Org_SBE
    INNER JOIN Companies ON Companies.ID = CompanyID
    WHERE (Awarded = 0) 
    AND Purpose LIKE COALESCE('%'+@_Purpose+'%', Purpose)
    AND CompanyID = COALESCE(@_CompanyID, CompanyID)
    AND CalendarYear =  COALESCE(@_CalYear, CalendarYear)
    AND Org_Division =  COALESCE(@_Division, Org_Division)
    AND Org_Group =  COALESCE(@_Group, Org_Group)
    AND AttAOverview LIKE COALESCE('%'+@_AttAOverview+'%', AttAOverview)
    AND NegNotes LIKE COALESCE('%'+@_NegNotes+'%', NegNotes)
    AND ExecutedBy LIKE COALESCE('%'+@_ExecutedBy+'%', ExecutedBy)
    AND PrimeSub =  COALESCE(@_PrimeSub, PrimeSub)
    I dragged the SP over to a dataset in my project and then went to preview the data by suppling a value for one of the optional parameters. I received an error message "Enter a value for parameter "_CompanyID". Upon further testing, Preview Data required values for all of the Int32 and Boolean parameters but then returned no records. I made all the parameters optional in SQL Server. Why is VS requiring the parameters???? Is there something I can add or change in the XSD???

    Thanks for any help!

    Wednesday, December 16, 2009 1:25 PM

All replies

  • Does anyone have an answer to this problem?

    Why is AllowDbNull="True" ignored in the XSD file?

    Is this a VS2005 bug? If so, is it corrected in VS2008? VS2010?

    Monday, December 28, 2009 8:25 PM