title: Return rowcount from stored procedure T-SQL
body: <p>Hello i got an agentjob checking what stored procedure to call. And sending XMLData from a table into the stored procedure. an Example of this</p>
  
  <pre><code>ELSE IF(@Entity = 'Payment') 
  BEGIN
  EXEC dbo.Payment @output output, @XmlDocument
  END
  </code></pre>
  
  <p>The XmlDocument working correct, but i would like to check rowcount from the stored procedure. But as you can see it aint working this way, it says "procedure has too many arguments specified". <strong>I need help with getting this variable back from the stored procedure.</strong> Thanks in advance!</p>
  
  <pre><code>create procedure dbo.Payment (@output int output, @XmlDocument XML)
  as
  begin
  Create Table #TempTable(IntComp VARCHAR(10), PaymentCode VARCHAR(10), PaymentDays int, [Text] VARCHAR(30), FreeMonth tinyint, PaymentCode2 VARCHAR(10))
  Declare @Identity as INT
  SET @Identity = (SELECT max ((etPayment.ID) + 1) from dbo.etPayment)
  IF @Identity IS NULL
      SET @Identity = 1
  
  BEGIN TRY
  WITH XMLNAMESPACES('http://bla.bla.Payment' AS ns0)
  INSERT INTO #TempTable (IntComp, PaymentCode, PaymentDays, [Text], FreeMonth, PaymentCode2)     
              SELECT 
                      @xmldocument.value('/ns0:Payment[1]/Payment[1]/IntComp[1]', 'VARCHAR(10)') IntComp,
                      @xmldocument.value('/ns0:Payment[1]/Payment[1]/PaymentCode[1]', 'VARCHAR(10)') PaymentCode,
                      @xmldocument.value('/ns0:Payment[1]/Payment[1]/PaymentDays[1]', 'int') PaymentDays,
                      @xmldocument.value('/ns0:Payment[1]/Payment[1]/Text[1]', 'VARCHAR(30)') [Text],
                      @xmldocument.value('/ns0:Payment[1]/Payment[1]/FreeMonth[1]', 'tinyint') FreeMonth,
                      @xmldocument.value('/ns0:Payment[1]/Payment[1]/PaymentCode2[1]', 'VARCHAR(10)') PaymentCode2
  
  
  MERGE dbo.etPayment AS Target
  USING 
  (
  
  SELECT 
  IntComp,
  PaymentCode,
  PaymentDays,
  [Text],
  FreeMonth,
  PaymentCode2
  FROM #TempTable) AS Source
  ON (Target.IntComp = Source.IntComp and Target.PaymentCode = Source.PaymentCode)
  WHEN MATCHED 
  THEN UPDATE SET 
  Target.PaymentDays = Source.PaymentDays,
  Target.[Text] = Source.[Text],
  Target.FreeMonth = Source.FreeMonth,
  Target.PaymentCode2 = Source.PaymentCode2
  
  WHEN NOT MATCHED BY TARGET 
  THEN INSERT(
  ID,
  IntComp,
  PaymentCode,
  PaymentDays,
  [Text],
  FreeMonth,
  PaymentCode2
  )
  
  VALUES(
  @Identity,
  Source.IntComp,
  Source.Paymentcode,
  Source.PaymentDays,
  Source.Text,
  Source.FreeMonth,
  Source.PaymentCode2
  );
  END TRY
  BEGIN CATCH
  SET @output = @@ROWCOUNT
  SELECT @output
  END CATCH
  DROP TABLE #TempTable;
  SET @output = @@ROWCOUNT 
  SELECT @output
  
  
  end
  </code></pre>