Mapping JDBC Adapter - SQL Server SP

[Former Member]

Hi,

I am executing a Stored procedure in SQL Server. I want to update morethan one record to a table at a time. my datatype occurences are 1..unbounded. But when I execute the procedure, it updates first record only. Do i need to write specific statement for that in the procedure?

Stored Procedure

-

---

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_NULLS ON

GO

CREATE PROCEDURE [sp_CustomerProc] (

@CustomerID [char](10),

@Name [char](25),

@Address [char](40),

@PhoneNo [char](10),

@QueryDetails [char](50))

AS If (Select customerid from customer_address where customerid=@CustomerID) IS NULL

BEGIN

INSERT INTO [SAPXI].[dbo].[customer_address]

([customerid],

[name],

[address],

[phone],

[querydetails])

VALUES

(@CustomerID,

@Name,

@Address,

@PhoneNo,

@QueryDetails)

END

ELSE

BEGIN

UPDATE [SAPXI].[dbo].[customer_address]

SET

[customerid]=@CustomerID,

[name]=@Name,

[address]=@Address,

[phone]=@PhoneNo,

[querydetails]=@QueryDetails WHERE [customerid]=@CustomerID

END

GO

SET QUOTED_IDENTIFIER OFF

GO

SET ANSI_NULLS ON

GO

The File Structure

========================

<?xml version="1.0" encoding="UTF-8"?>

<ns0:MT_customer_address_file xmlns:ns0="urn:file2jdbc_sp">

<CustomerDetails>

<CustomerID>1005</CustomerID>

<Name>Customer5</Name>

<Address>Chennai</Address>

<Phone>537653265</Phone>

<QueryDetails>Is this working?</QueryDetails>

</CustomerDetails>

<CustomerDetails>

<CustomerID>1006</CustomerID>

<Name>Customer6</Name>

<Address>Chennai</Address>

<Phone>537653265</Phone>

<QueryDetails>Is this working?</QueryDetails>

</CustomerDetails>

</ns0:MT_customer_address_file>

Please advice.

Thanks,

Sri.

[STALANKI]

check this blog which will be helpful for executing stored procs.

/people/sriram.vasudevan3/blog/2005/02/14/calling-stored-procs-in-maxdb-using-sap-xi

[Former Member]

Hi,

In this case I am able to execute the stored peocedure and is inserting data into table. But when I am passing morethan one record, it is just updating first record in database.

Please advice.

Thanks,

Sri.

[Former Member]

Hi Sri ,

First execute the stored proc from the backend by passing the appropriate parameters and check if you are getting the desired results in the table.

If the table is being updated properly, then check your XML document format. Check out the following link.

http://help.sap.com/saphelp_nw2004s/helpdata/en/4d/8c103e05df2e4b95cbcc68fed61705/content.htm

Thanks,

Gaurav