Storedprocedure Parameter starting with @

[Former Member]

Hi,

I need to call a stored procedure on a MS SQL database. Here parameters are denoted with a prefix of @. That leaves me with a structure for the JDBC adapter like:

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

<Statement>

<storedProcedureName action="EXECUTE">

<table>spVMPGetimportID</table>

<@ImportId>1000</@ImportId>

</storedProcedureName>

</Statement>

This is No good, since it's invalid with @ in the start of a tag.

Any ideas?

Kind regards

Mikkel

[Former Member · Accepted solution]

Hi Mikkel,

In stored procedure only user defined variable is prefix by @ symbol. While defining the stored procedure all the variable which is user defined needs to be prefixed by @. Same is here in the example, the ImportID is a variable and thats the reason @ is prefixed on it.

Remove the @ symbol and check it or use simple SQL statements instead of stored procedure.

Regards,

Subhasha

[former_member192892]

Hmm Mikkel,

In this situation,a work around i see is to use action = "SQL_QUERY" or "SQL_DML" and to use a UDF to generate the whole SQL statement and pass it...

The receiver data structure wud be

<StatementName6>

<anyName action=” SQL_QUERY” | “SQL_DML”>

<access>SQL-String with optional placeholder(s)</access>

<key>

<placeholder1>value1</placeholder1>

<placeholder2>value2<placeholder2>

</key>

</anyName >

</StatementName6>