SQL Adapter – SPROC Help??

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › SQL Adapter – SPROC Help??

Viewing 2 reply threads
  • Author
    Posts
    • September 27, 2006 at 5:52 AM #15861
      community-content

        Hi,

        here is my SPROC:

        CREATE PROCEDURE SP_Mortage_Select
        (@Loan_Num nvarchar(20)
         @LnndCaseNum int output,
         @LnAmount decimal output
        )
        AS
        BEGIN
        SELECT  @LnndCaseNum = Loan.LoanID,
         @LnAmount = Mortage.LoanAmt,
         FROM Loan
         JOIN MortgageTerms on Mortage.MortageType = Loan.MortageType
         WHERE Loan.LoanNumber = @Loan_Num FOR XML AUTO, XMLDATA

        END
        GO

        i am getting error: The FOR XML clause is not allowed in a ASSIGNMENT statement.

        i am not database person!! 

        any help is appreciated.

        Thanks
        Ray- 

         

      • September 27, 2006 at 8:24 AM #15867
        community-content

          Not sure why you need to select into params.  Just select the columns and then promote the schema attributes if you need to use those values.

          SELECT  Loan.LoanID,
             Mortage.LoanAmt
           FROM Loan
           JOIN MortgageTerms on Mortage.MortageType = Loan.MortageType
           WHERE Loan.LoanNumber = @Loan_Num FOR XML AUTO, XMLDATA

           Or if you need those names…

          SELECT Loan.LoanID as [LnndCaseNum],
               Mortage.LoanAmt AS [LnAmount ]
           FROM Loan
           JOIN MortgageTerms on Mortage.MortageType = Loan.MortageType
           WHERE Loan.LoanNumber = @Loan_Num FOR XML AUTO, XMLDATA

           

          No need to put them to vairables unless you explain why you wanted that.

          • September 27, 2006 at 6:23 PM #15876
            community-content

              Thanx dave, the second one workes for me.

              i don't need that variables.

          • September 27, 2006 at 9:02 AM #15868
            community-content

              Can you execute the procedure outside of BizTalk?

               Is there a reason you are assigning the results to output parameters

               Try

              Select Loan.LoanID, Mortgage.LoanAmount From…

          • Author
            Posts
          Viewing 2 reply threads
          • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.