CODEWITHSUNDEEP
sqlt-sqlstored-proceduresrowcount
Girre
Girre

Reputation: 47

Return rowcount from stored procedure T-SQL

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

ELSE IF(@Entity = 'Payment') 
BEGIN
EXEC dbo.Payment @output output, @XmlDocument
END

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". I need help with getting this variable back from the stored procedure. Thanks in advance!

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

Upvotes: 1

Views: 2608

Answers (1)

ughai
ughai

Reputation: 9890

I need help with getting this variable back from the stored procedure.

you need to move the SET @output = @@ROWCOUNT just after the MERGE statement and remove other statement setting the variable @output. Something like this.

http://sqlfiddle.com/#!6/99e0b/5

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
);
SET @output = @@ROWCOUNT;
END TRY
BEGIN CATCH
SET @output = 0;
SELECT @output
END CATCH
DROP TABLE #TempTable;
end

You can call the procedure like this and get the @output

DECLARE @output int,@XmlDocument XML= '<?xml version="1.0" encoding="UTF-8"?>
<ns0:Payment  xmlns:ns0="http://bla.bla.Payment">
    <Payment>
        <IntComp>asasc</IntComp>
        <PaymentCode>P</PaymentCode>
        <PaymentDays>20</PaymentDays>
        <FreeMonth>1</FreeMonth>
        <PaymentCode2>P</PaymentCode2>
        <Text>TEXT</Text>
        </Payment>
    </ns0:Payment>'

EXEC dbo.Payment @output output,@XmlDocument

SELECT @output as output,* FROM etPayment;

Upvotes: 2

Related Questions